October 13, 2010 at 11:53 am
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!!!
October 13, 2010 at 12:13 pm
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)
October 13, 2010 at 12:27 pm
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?
October 13, 2010 at 12:36 pm
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
October 13, 2010 at 12:45 pm
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
October 13, 2010 at 1:08 pm
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
October 13, 2010 at 1:33 pm
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
October 19, 2010 at 8:45 am
--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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply