Exploring the DATE Functions in SQL
-
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/ -
I would use SYSDATETIME() instead of GETDATE() in examples as a better choice these days
-
There is an EOMONTH function to get you the end of the month.
-
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
- 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.
-
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/ -
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/ -
This article is great as a crash course on Dates in TSQL. Glad it got pushed out in the newsletter this morning. Thanks @aveek22! 🙂
- 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.
-
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
-
No doubt. But the EOMONTH function could have been introduced with the presented one as an alternative.
-
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.
-
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/ - 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.
- 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 19 total)
You must be logged in to reply to this topic. Login to reply