SQL to get dates for Reporting Services

Question

Post reply

SQL to get dates for Reporting Services

DucatiRider

SSC-Addicted

Points: 406
More actions
October 13, 2010 at 11:53 am

#241714

Hi, hoping someone can help me out a bit as my SQL isn't the strongest. I'm creating a dataset and trying to make some basic fields that I can use for report parameters as well. I want to make 2 fields, 1 for 1/1/year and one field for the last day of the prior month. What I'm planning on using for my last day of the prior month field is this:
dateadd(mm,0,convert(datetime,convert(varchar,right(year(getdate()),2)+right('0'+convert(varchar,month(getdate())),2) +'01')))-1. My question is....in January, will this give me December 31?
I'm going to use this in a YTD report and I need to make sure that when the report runs next January,(2011) I need to be using a time frame of 1/1/2010 - 12/31/2010 as the range....for the rest of the months of the year, (Feb - Dec, 2011) the time frame will use 1/1 of the current year thru the last day of the prior month of the current year. Hope that isn't too confusing, any help would be greatly appreciated!!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

select

LastDayCurrentMonth= dateadd(mm,datediff(mm,-1,getdate()),-1),

LastDayPriorMonth= dateadd(mm,datediff(mm,-1,getdate())-1,-1)

Results:

LastDayCurrentMonth LastDayPriorMonth

----------------------- -----------------------

2010-10-31 00:00:00.000 2010-09-30 00:00:00.000

(1 row(s) affected)

DucatiRider SSC-Addicted Points: 406 More actions · Answer 2

Thanks for the reply! How can I get a 1/1 date for the current year that will return 1/1 of the prior year when I use it in January?

WayneS SSC Guru Points: 95405 More actions · Answer 3

Check out the link in my signature for common date/time routines. You'll also need to use a case statement to handle whether the current month is January for which routine you need to use.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

DucatiRider (10/13/2010)
Thanks for the reply! How can I get a 1/1 date for the current year that will return 1/1 of the prior year when I use it in January?

Your question is not clear, but look at this.

select

FirstDayCurrentYear= dateadd(yy,datediff(yy,00,getdate()),0),

FirstDayLastYear= dateadd(yy,datediff(yy,00,getdate())-1,0)

Results:

FirstDayCurrentYear FirstDayLastYear

----------------------- -----------------------

2010-01-01 00:00:00.000 2009-01-01 00:00:00.000

DucatiRider SSC-Addicted Points: 406 More actions · Answer 5

Thanks for all the help! Here's what I came up with for my January 1 date field:

CASE WHEN month(getdate()) = 1 THEN DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) ELSE DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) END AS BegOfRollingYear

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

You are making it too complex.

select

*,

FirstDayRollingYear=

dateadd(yy,datediff(yy,0,dateadd(mm,-1,a.DT)),0),

LastDayRollingYear=

dateadd(yy,datediff(yy,-1,dateadd(mm,-1,a.DT)),-1)

from

( -- Test Data

select DT = convert(datetime,'2010-01-01')union all

select DT = convert(datetime,'2010-01-31')union all

select DT = convert(datetime,'2010-02-01')union all

select DT = convert(datetime,'2010-03-31')union all

select DT = convert(datetime,'2010-12-31')

) a

order by

a.DT

Results:

DT FirstDayRollingYear LastDayRollingYear

------------------------ ----------------------- -----------------------

2010-01-01 00:00:00.000 2009-01-01 00:00:00.000 2009-12-31 00:00:00.000

2010-01-31 00:00:00.000 2009-01-01 00:00:00.000 2009-12-31 00:00:00.000

2010-02-01 00:00:00.000 2010-01-01 00:00:00.000 2010-12-31 00:00:00.000

2010-03-31 00:00:00.000 2010-01-01 00:00:00.000 2010-12-31 00:00:00.000

2010-12-31 00:00:00.000 2010-01-01 00:00:00.000 2010-12-31 00:00:00.000

Jay_Noob SSCommitted Points: 1565 More actions · Answer 7

--DECLARE VARS

declare @date datetime

,@no_time datetime-- time stripped off

,@fdom datetime-- first day of month

,@ldom datetime-- last day of month

,@fdoy datetime-- first day of year

,@ldoy datetime-- last day of year

--ASSIGN VARS

select @date = '20100915 12:12:12.121'

select @no_time = CAST(FLOOR(CAST(@date AS float)) AS datetime)

select @fdom = DATEADD(dd, 1 - DAY(@no_time), @no_time)

select @ldom = DATEADD(ms, -7, DATEADD(mm, 1, @fdom))

select @fdoy = DATEADD(mm, 1 - MONTH(@fdom), @fdom)

select @ldoy = DATEADD(ms, -7, DATEADD(yy, 1, @fdoy))

--RESULT

select @date as [date]

,@no_time as [no_time]

,@fdom as first_day_month

,@ldom as last_day_month

,@fdoy as first_day_year

,@ldoy as last_day_year

An interesting little tidbit is that if you subtract 1 millisecond from midnight, sql server returns 23:59:59.993 of the previous day. That's why I use -7 in the @ldom and @ldoy assignments.