Naming a date range based on a Start and end date

Question

Naming a date range based on a Start and end date

Chrissy321

SSCoach

Points: 15763
More actions
June 27, 2012 at 10:47 am

#276969

Hello All,
Given a start and end date I am trying to determine if the range defined by these dates can be classified into one of three types of named ranges.
The three named ranges are: 1YR, QTR, and YTD (one-year, quarter, and year-to-date)
The logic:
-if the range does not fall into one of my three I would like to return NULL
-both start and end date must be the last day of a month otherwise null
-1YR-a one year difference between start and end dates
-QTR-a three month/one quarter difference between start and end dates
-YTD-the startdate must be 12/31
-If the QTR and YTD are both met I have no preference on what is returned.
I think my solution is working. I was hoping someone with more experience than I would be able to see if there are any flaws in my logic or if there were any possible simplifications.
Thanks.
CREATE TABLE #Test
(
StarteDate datetime,
EndDate datetime
)
INSERT INTO #Test (StarteDate,EndDate) VALUES ('9/30/2011','12/31/2011')
INSERT INTO #Test (StarteDate,EndDate) VALUES ('12/31/2010','12/31/2011')
INSERT INTO #Test (StarteDate,EndDate) VALUES ('12/31/2010','3/31/2011')
INSERT INTO #Test (StarteDate,EndDate) VALUES ('1/31/2010','3/31/2011')
INSERT INTO #Test (StarteDate,EndDate) VALUES ('3/31/2010','6/30/2011')
INSERT INTO #Test (StarteDate,EndDate) VALUES ('3/31/2011','6/30/2011')
INSERT INTO #Test (StarteDate,EndDate) VALUES ('12/31/2010','4/30/2011')
INSERT INTO #Test (StarteDate,EndDate) VALUES ('3/31/2010','3/31/2011')
INSERT INTO #Test (StarteDate,EndDate) VALUES ('3/15/2010','3/15/2011')
INSERT INTO #Test (StarteDate,EndDate) VALUES ('2/28/2011','2/29/2012')
SELECT
StarteDate,
EndDate,
--Check if noth start and end dates are month ends
CASE
WHEN
(DATEPART(DAY,DATEADD(DAY,1,StarteDate)) = 1 AND
DATEPART(DAY,DATEADD(DAY,1,EndDate)) = 1)
THEN
CASE
WHEN
DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy,1,StarteDate) ) + 1, 0))= EndDate
THEN '1YR'
WHEN
DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(qq,1,StarteDate) ) + 1, 0))= EndDate
THEN 'QTR'
WHEN
((DATEPART(mm,StarteDate) = 12) AND
(DATEPART(dd,StarteDate) = 31))
THEN 'YTD'
ELSE NULL
END
END
AS NamedRange
FROM #Test
DROP TABLE #Test

Viewing 4 posts - 1 through 3 (of 3 total)

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Code looks OK to me.

Regarding date formatting, I'd suggest using 'YYYYMMDD', which is foolproof on SQL Server, rather than 'YYYY-MM-DD', which can fail depending on specific settings in SQL Server.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

ScottPletcher (6/27/2012)
Code looks OK to me.
Regarding date formatting, I'd suggest using 'YYYYMMDD', which is foolproof on SQL Server, rather than 'YYYY-MM-DD', which can fail depending on specific settings in SQL Server.

Agreed, interesting article here

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

CELKO (6/27/2012)
ScottPletcher (6/27/2012)
Regarding date formatting, I'd suggest using 'YYYYMMDD', which is foolproof on SQL Server, rather than 'YYYY-MM-DD', which can fail depending on specific settings in SQL Server.
NO! That is needless dialect, which is always bad coding. Did you see the default in the DATE data type casting? There is a reason.
When we set up the DATE data type, we allowed one and only one display format from the ISO-8601 Standard. We settled on the 'yyyy-mm-dd hh:mm:ss.sssssss' where the seven decimals are from the FIPS-127 standards.

If for some reason you really feel compelled to use the yyyy-mm-dd style in SQL Server, you need to use this format:

yyyy-mm-ddThh:mi:ss[.mmm] --you must specify thru the seconds, but can omit subsecs

that format is safe, but just yyyy-mm-dd is NOT in SQL Server, viz:

SET DATEFORMAT dmy

SELECT CAST('20120627' AS datetime) --ALWAYS works, regardless of any SQL settings

SELECT CAST('2012-06-27T00:00:00' AS datetime)--ALWAYS works

SELECT CAST('2012-06-27' AS datetime)--WARNING!! -- IS DEPENDENT ON SQL SETTINGS

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".