468,771 Members | 1,827 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,771 developers. It's quick & easy.

Job to be run ever first for the previous calendar month

Hi!

I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?

Thank you,
T.

Jan 17 '07 #1
5 2310
tolcis,

There might be a more elegant way, but this should work:

declare @Now datetime
declare @StartDate datetime
declare @EndDate datetime

set @Now = getdate()
if datepart(d, @Now) = 1 -- only execute if it is the first day of the month
begin
-- to get start date, subtract one month from the date and remove the
time from the date
set @StartDate = convert(varchar(15), dateadd (m, -1, @now), 112)
-- to get end date, remove time from date
set @EndDate = dateadd(m, 1, @StartDate)
print cast(@StartDate as char(25)) + cast(@EndDate as char(25))
-- execute your code here using >= @StartDate and < @EndDate
end

-- Bill

"tolcis" <ny********@gmail.comwrote in message
news:11**********************@11g2000cwr.googlegro ups.com...
Hi!

I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?

Thank you,
T.

Jan 18 '07 #2
WHERE create_date < DATEADD(month,DATEDIFF(month, 0,getdate()),0)
AND create_date >= DATEADD(month,DATEDIFF(month, 0,getdate())-1,0)

Roy Harvey
Beacon Falls, CT

On 17 Jan 2007 14:30:29 -0800, "tolcis" <ny********@gmail.comwrote:
>Hi!

I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?

Thank you,
T.
Jan 18 '07 #3
tocis,

Ignore the first post, this is better. Schedule your job to be run on the
first of every month. This will give you the prior month date range for any
date, not just the 1st. This way if the job fails you can also run it on the
2nd, 3rd, etc.

declare @Now datetime
declare @StartDate datetime
declare @EndDate datetime

set @Now = getdate()

-- to get end date, subtract days to get to get 1st of the month for any
date
set @EndDate = dateadd(d, 1 - datepart(d,@Now), @Now)

-- remove the time portion
set @EndDate = convert(char(12), @EndDate, 112)

-- subtract a month for start date
set @StartDate = dateadd(m, -1, @EndDate)
print cast(@StartDate as char(25)) + cast(@EndDate as char(25))
-- execute your code here using >= @StartDate and < @EndDate

-- Bill

"AlterEgo" <al********@dslextreme.comwrote in message
news:12*************@corp.supernews.com...
tolcis,

There might be a more elegant way, but this should work:

declare @Now datetime
declare @StartDate datetime
declare @EndDate datetime

set @Now = getdate()
if datepart(d, @Now) = 1 -- only execute if it is the first day of the
month
begin
-- to get start date, subtract one month from the date and remove the
time from the date
set @StartDate = convert(varchar(15), dateadd (m, -1, @now), 112)
-- to get end date, remove time from date
set @EndDate = dateadd(m, 1, @StartDate)
print cast(@StartDate as char(25)) + cast(@EndDate as char(25))
-- execute your code here using >= @StartDate and < @EndDate
end

-- Bill

"tolcis" <ny********@gmail.comwrote in message
news:11**********************@11g2000cwr.googlegro ups.com...
>Hi!

I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?

Thank you,
T.


Jan 18 '07 #4
Roy,

Wya cool, did not know this!

-- Bill

"Roy Harvey" <ro********@snet.netwrote in message
news:vi********************************@4ax.com...
WHERE create_date < DATEADD(month,DATEDIFF(month, 0,getdate()),0)
AND create_date >= DATEADD(month,DATEDIFF(month, 0,getdate())-1,0)

Roy Harvey
Beacon Falls, CT

On 17 Jan 2007 14:30:29 -0800, "tolcis" <ny********@gmail.comwrote:
>>Hi!

I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?

Thank you,
T.

Jan 18 '07 #5
Thanks. Works great.
Roy Harvey wrote:
WHERE create_date < DATEADD(month,DATEDIFF(month, 0,getdate()),0)
AND create_date >= DATEADD(month,DATEDIFF(month, 0,getdate())-1,0)

Roy Harvey
Beacon Falls, CT

On 17 Jan 2007 14:30:29 -0800, "tolcis" <ny********@gmail.comwrote:
Hi!

I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?

Thank you,
T.
Jan 18 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.