Has anyone ever created a UDF for a rolling calendar year? I would like to
use that instead of writing this kind of stuff all the time:
if(datepart(month,@.CurrentDate) =12)
Begin
set @.PastDate = '1/1/'+convert(varchar, @.SelectedYear)
end
if(datepart(month,@.CurrentDate) !=12)
Begin
set @.PastDate = dateadd(month,1,@.PastDate)
End
set @.CurrentDate = dateadd(month,1,@.Currentdate)
if(datepart(day, @.CurrentDate) <= 30)
Begin
set @.CurrentDateStep = dateadd(day,1,@.CurrentDate)
if datepart(month,@.CurrentDateStep) = datepart(month,@.CurrentDate)
Begin
set @.currentDate = @.CurrentDateStep
end
if datepart(month, @.CurrentDate) = 3
Begin
set @.CurrentDate = dateadd(day,2,@.CurrentDate)
end
end
Thanks in advance for any assistance.
Thanks,
SPHow about a calendar table?
http://www.aspfaq.com/2519
"SP" <SP@.discussions.microsoft.com> wrote in message
news:8E1056B9-3011-4523-894A-1087FE936B1A@.microsoft.com...
> Has anyone ever created a UDF for a rolling calendar year? I would like to
> use that instead of writing this kind of stuff all the time:
> if(datepart(month,@.CurrentDate) =12)
> Begin
> set @.PastDate = '1/1/'+convert(varchar, @.SelectedYear)
> end
> if(datepart(month,@.CurrentDate) !=12)
> Begin
> set @.PastDate = dateadd(month,1,@.PastDate)
> End
> set @.CurrentDate = dateadd(month,1,@.Currentdate)
> if(datepart(day, @.CurrentDate) <= 30)
> Begin
> set @.CurrentDateStep = dateadd(day,1,@.CurrentDate)
> if datepart(month,@.CurrentDateStep) = datepart(month,@.CurrentDate)
> Begin
> set @.currentDate = @.CurrentDateStep
> end
> if datepart(month, @.CurrentDate) = 3
> Begin
> set @.CurrentDate = dateadd(day,2,@.CurrentDate)
> end
> end
> Thanks in advance for any assistance.
> Thanks,
> SP
>|||Hi,
Pardon me for my ignorance, can you please elucidate with an example what
you are trying to achieve?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Sure. Sorry for the sparse information.
At the company that I work for, they are always interested in a rolling
calendar year. When I write reports with Reporting Services, they may run
them for say 2006. The end date would always change depending on what month
the report is run. For example, if the report is run today, they would want
the results to show 6/1/2005 to 5/31/2006. If it gets run next month,
7/1/2005 to 6/30/2006, etc.
I always have to wrote some funky code to consider February, etc.
I just thought if someone had written a UDF, that might help. Or send me in
the right direction.
I hope this clarifies things a little and I will definitely take a look at
the Date table. I have done similiar things with an Access database, but
didn't think about it for work.
Thanks to all for looking.
SP
"Omnibuzz" wrote:
> Hi,
> Pardon me for my ignorance, can you please elucidate with an example wha
t
> you are trying to achieve?
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Maybe, this should work.. If I understood it wrong.. correct me..
declare @.a datetime
set @.a = getdate() -- Date on which you are running the report
select
dateadd(mm,datediff(mm,0,dateadd(yy,-1,@.a)),0) as Begin_dt,
dateadd(mm,datediff(mm,0,@.a),0)-1 as end_dt
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||As written, the End_dt is midnight on the last day of the month, effectively
leaving out all entries for that day.
I would add one more dateadd step to include ALL times on the last day of th
e month (up to 23:59:59.997)
DECLARE @.Today datetime
SET @.Today = getdate() -- Date on which you are running the report
SELECT
dateadd( mm, datediff( mm, 0, dateadd( yy, -1, @.Today )), 0 ) AS 'Begin_Dt'
, dateadd( ms, -2, dateadd(mm, datediff( mm, 0, @.Today ), 0 ) ) AS 'End_Dt'
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message news:30BC1C01-180E-442C-8D
BE-15E9B60B0C08@.microsoft.com...
> Maybe, this should work.. If I understood it wrong.. correct me..
>
> declare @.a datetime
> set @.a = getdate() -- Date on which you are running the report
> select
> dateadd(mm,datediff(mm,0,dateadd(yy,-1,@.a)),0) as Begin_dt,
> dateadd(mm,datediff(mm,0,@.a),0)-1 as end_dt
>
> --
> -Omnibuzz (The SQL GC)
>
> http://omnibuzz-sql.blogspot.com/
>
>|||I think it would be cleaner to use the next month's start instead of 3 ms
earlier, and use < on the end_dt instead of <= (and obviously, don't use
between).
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23KeXiJIlGHA.4660@.TK2MSFTNGP05.phx.gbl...
As written, the End_dt is midnight on the last day of the month, effectively
leaving out all entries for that day.
I would add one more dateadd step to include ALL times on the last day of
the month (up to 23:59:59.997)
DECLARE @.Today datetime
SET @.Today = getdate() -- Date on which you are running the report
SELECT
dateadd( mm, datediff( mm, 0, dateadd( yy, -1, @.Today )), 0 ) AS
'Begin_Dt'
, dateadd( ms, -2, dateadd(mm, datediff( mm, 0, @.Today ), 0 ) ) AS
'End_Dt'
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:30BC1C01-180E-442C-8DBE-15E9B60B0C08@.microsoft.com...
> Maybe, this should work.. If I understood it wrong.. correct me..
> declare @.a datetime
> set @.a = getdate() -- Date on which you are running the report
> select
> dateadd(mm,datediff(mm,0,dateadd(yy,-1,@.a)),0) as Begin_dt,
> dateadd(mm,datediff(mm,0,@.a),0)-1 as end_dt
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Agreed.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhBC5SIlGHA.984@.TK2MSFTNGP05.phx.gbl...
>I think it would be cleaner to use the next month's start instead of 3 ms
>earlier, and use < on the end_dt instead of <= (and obviously, don't use
>between).
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23KeXiJIlGHA.4660@.TK2MSFTNGP05.phx.gbl...
> As written, the End_dt is midnight on the last day of the month,
> effectively leaving out all entries for that day.
> I would add one more dateadd step to include ALL times on the last day of
> the month (up to 23:59:59.997)
> DECLARE @.Today datetime
> SET @.Today = getdate() -- Date on which you are running the report
> SELECT
> dateadd( mm, datediff( mm, 0, dateadd( yy, -1, @.Today )), 0 ) AS
> 'Begin_Dt'
> , dateadd( ms, -2, dateadd(mm, datediff( mm, 0, @.Today ), 0 ) ) AS
> 'End_Dt'
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:30BC1C01-180E-442C-8DBE-15E9B60B0C08@.microsoft.com...
>
sql
