You are here: Re: Job to be run ever first for the previous calendar month « MsSQL Server « IT news, forums, messages
Re: Job to be run ever first for the previous calendar month

Posted by AlterEgo on 01/18/07 00:02

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" <alterego55@dslextreme.com> wrote in message
news:12qte0qjqivt0ae@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" <nytollydba@gmail.com> wrote in message
> news:1169073028.098703.235980@11g2000cwr.googlegroups.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.
>>
>
>

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация