• 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


    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