mtassin (5/10/2012)
I don't get how DATETIMEFROMPARTS can tell if I want the first day of the quarter, or the first day of a month based on the portion of the article that has
SELECT SomeDate,
DATETIMEFROMPARTS(ca.Yr, 1, 1, 0, 0, 0, 0) AS [FirstDayOfYear],
DATETIMEFROMPARTS(ca.Yr, ca.Mth, 1, 0, 0, 0, 0) AS [FirstDayOfMonth],
DATETIMEFROMPARTS(ca.Yr, ca.Qtr, 1, 0, 0, 0, 0) AS [FirstDayOfQuarter],
DATETIMEFROMPARTS(ca.Yr, ca.Mth, ca.Dy, ca.Hr, 0, 0, 0) AS StartOfHour,
DATETIMEFROMPARTS(ca.Yr, ca.Mth, ca.Dy, ca.Hr, ca.Mn, 0, 0) AS StartOfMinute,
DATETIMEFROMPARTS(ca.Yr, ca.Mth, ca.Dy, ca.Hr, ca.Mn, ca.Sec, 0) AS StartOfSecond
In the 3rd line you pass in an integer from the Quarter, but every other time it's the month.
What did you do with the quarter line that lets DATETIMEFROMPARTS know that it's using a quarter instead of a month?
I mean if I run
SELECT DATETIMEFROMPARTS(2012, 3, 1, 0, 0, 0, 0) AS [FirstDayOfQuarter]
Is that 2012-03-01 or is it 2012-07-01 ?
Excellent question Mark. Actually, in the Qtr column in the CROSS APPLY, I use this calculation to determine what the month number is at the beginning of the quarter for the date:
((CEILING(MONTH(dt.SomeDate)/3.0)*3)-2) AS Qtr,
In the example that you posted, it is specifying the third month, thus 2012-03-01.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes