Exploring the DATE Functions in SQL

  • aveek22

    SSC-Addicted

    Points: 484

    Comments posted to this topic are about the item Exploring the DATE Functions in SQL

    Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
    https://twitter.com/avikoleum
    https://www.linkedin.com/in/aveekd22/

  • beervolk

    Grasshopper

    Points: 13

    I would use SYSDATETIME() instead of GETDATE() in examples as a better choice these days

  • RonKyle

    SSC-Dedicated

    Points: 31482

    There is an EOMONTH function to get you the end of the month.

  • tellery_frmt

    Newbie

    Points: 7

    would it be easier (more accurate) to calculate age in months and divide by 12?

    select cast( datediff( month,'08-21-2008',getdate()) as decimal)/12

  • thisisfutile

    Hall of Fame

    Points: 3496

    beervolk wrote:

    I would use SYSDATETIME() instead of GETDATE() in examples as a better choice these days

    I've never used sysdatetime.  I'm genuinely curious why you recommend this.

  • aveek22

    SSC-Addicted

    Points: 484

    Thank you, @beervolk. Could you please also explain briefly why do you prefer to use sysdatetime over getdate?

    Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
    https://twitter.com/avikoleum
    https://www.linkedin.com/in/aveekd22/

  • aveek22

    SSC-Addicted

    Points: 484

    Thank you, @tellery_frmt for your comment.

    If you use months to calculate the DOB, then your age will inflate at the start of your birth month, which is not correct. See the following example.

    Birthdate is 22-Dec-1992, and so 22-Dec-2020 he should be 28 years old. However, if you use your calculation, even on 01-Dec-2020, the age is calculated as 28 years.

    Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
    https://twitter.com/avikoleum
    https://www.linkedin.com/in/aveekd22/

  • Brian F

    SSC Rookie

    Points: 36

    This article is great as a crash course on Dates in TSQL. Glad it got pushed out in the newsletter this morning. Thanks @aveek22! 🙂

  • Lynn Pettis

    SSC Guru

    Points: 442337

    RonKyle wrote:

    There is an EOMONTH function to get you the end of the month.

    Yes, we do have the EOMONTH function, but it is good to know how to calculate the EOM should you find yourself working with an older version of SQL Server where that function does not exist.

     

  • beervolk

    Grasshopper

    Points: 13

    Hello aveek22.

    GETDATE() is pretty old function dealing with DATETIME type.

    DATETIME is 8 bytes in storage and min value is 1753-01-01 🙁

    At the same time SYSDATETIME() deals with DATETIME2 witch can be 6 bytes in storage for DATETIME2(0) and 8 bytes for DATETIME(7). Plus we can set columns of DATETIME2 starting 0001-01-01 that can be useful in some cases.

    Many new functions work with DATETIME2 type.

    So, when designing new DB, writing examples it is better to use SYSDATETIME() - DATETIME2(0) if we do not need second fractions, DATETIME2(3) to be identical to DATETIME type and DATETIME2(7) to have seconds fraction of 7 digits.

    IMHO 🙂

    PS: I do not use GETDATE() at all 🙂 Only SYSDATETIME() and if I need DATETIME column in a table it will be DATETIME2 for sure 😉 and mostly DATETIME2(0), like for audit records. For transaction records it can have some seconds fractions

    • This reply was modified 4 months, 2 weeks ago by  beervolk.
    • This reply was modified 4 months, 2 weeks ago by  beervolk.
  • RonKyle

    SSC-Dedicated

    Points: 31482

    No doubt.  But the EOMONTH function could have been introduced with the presented one as an alternative.

  • Lynn Pettis

    SSC Guru

    Points: 442337

    Probably, but even with EOMONTH available I still find myself calculating it since I have been doing that way for so long.  You just have to look at my inactive blog here on ssc to see that.

  • Michael L John

    One Orange Chip

    Points: 25929

    There is a potential gotcha with this syntax:

    WHERE [Invoice Date Key] BETWEEN '2014-01-01' AND '2014-12-01'

    The data types of the table in this example are of "date" types. It will work as expected. But, I have seen this syntax cause significant issues when the data type of the column is a smalldatetime, datetime, or datetime2.

    This syntax fails to consider the time portion of the dates, so it will cut off the last day's worth of data in the range.  In this example, if the column was a datetime type, there would be no data included for 12/1/2014 after midnight.

    Coincidentally, the best story I can relate about this issue was a monthly sales report.  The accounting folks came to me and asked why the sum of the 12 months of the reports never match up to the numbers if they run the report for an entire year.

    The last day of every month was essentially cut off.  And, it had been like that for 6 years.  The company had been under-reporting income for 6 years.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Lynn Pettis

    SSC Guru

    Points: 442337

    Michael L John wrote:

    There is a potential gotcha with this syntax:

    WHERE [Invoice Date Key] BETWEEN '2014-01-01' AND '2014-12-01'

    The data types of the table in this example are of "date" types. It will work as expected. But, I have seen this syntax cause significant issues when the data type of the column is a smalldatetime, datetime, or datetime2.

    This syntax fails to consider the time portion of the dates, so it will cut off the last day's worth of data in the range.  In this example, if the column was a datetime type, there would be no data included for 12/1/2014 after midnight.

    Coincidentally, the best story I can relate about this issue was a monthly sales report.  The accounting folks came to me and asked why the sum of the 12 months of the reports never match up to the numbers if they run the report for an entire year.

    The last day of every month was essentially cut off.  And, it had been like that for 6 years.  The company had been under-reporting income for 6 years.

    Which is why I use a semi-open date range for this purpose, closed at the start of the range and open at the end of the range.  That way it works regardless of the data type of the date column.

     

  • Michael L John

    One Orange Chip

    Points: 25929

    Lynn Pettis wrote:

    Michael L John wrote:

    There is a potential gotcha with this syntax:

    WHERE [Invoice Date Key] BETWEEN '2014-01-01' AND '2014-12-01'

    The data types of the table in this example are of "date" types. It will work as expected. But, I have seen this syntax cause significant issues when the data type of the column is a smalldatetime, datetime, or datetime2.

    This syntax fails to consider the time portion of the dates, so it will cut off the last day's worth of data in the range.  In this example, if the column was a datetime type, there would be no data included for 12/1/2014 after midnight.

    Coincidentally, the best story I can relate about this issue was a monthly sales report.  The accounting folks came to me and asked why the sum of the 12 months of the reports never match up to the numbers if they run the report for an entire year.

    The last day of every month was essentially cut off.  And, it had been like that for 6 years.  The company had been under-reporting income for 6 years.

    Which is why I use a semi-open date range for this purpose, closed at the start of the range and open at the end of the range.  That way it works regardless of the data type of the date column.

    Exactly.  I have seen this syntax bite the unsuspecting thousands of times in my almost 30 years.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 15 posts - 1 through 15 (of 20 total)

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