November 25, 2015 at 12:00 am
Comments posted to this topic are about the item Stairway to Advanced T-SQL Level 9: Compare, Modify, Derive and Validate Date and Time Values
Gregory A. Larsen, MVP
November 25, 2015 at 5:56 am
I have found it more important to understand how the tools my users use interpret date and time fields. Some older but widely used tools don't work well with Date and Time fields and smalldatetime or DateTime is the only field type they can dependably use.
November 25, 2015 at 6:50 am
Good article. I didn't realize that there was an EOMONTH function. I'm on 2008 R2 and could use that function, a lot.
November 25, 2015 at 7:49 am
I have a question for the community. What's the reasoning that DATE fields can't simply have a number added to them without the use of the DATEADD function? I assume it comes down to implicit conversions? This arithmetic always was (and still is) possible with DATETIME fields.
I'm a late comer to SQL 2008 and later versions, so my introduction to DATE and TIME occurred recently. I was quite surprised to see all of my old scripts fail when I simply wanted to add a +1 to a DATE field and received "Operand type clash: date is incompatible with int". I then discovered that DATEADD was necessary. To me, it's much easier to read and type "MyDateField + 1".
November 25, 2015 at 7:50 am
Thanks for the good article.
Just as a comment (not on your article specifically), did you notice that the EOMONTH returns a DATE instead of what the tooltip reads (DATETIME)? If you run the following to get the last accessible nanosecond (DATETIME type) for the end of the month (my typical report queries), you get an error.
SELECT DATEADD(ms,-3,DATEADD(dd,1,EOMONTH(GETDATE())))
Msg 9810, Level 16, State 1, Line 1
The datepart millisecond is not supported by date function dateadd for data type date.
You actually have to convert it to a DATETIME to get the value.
SELECT DATEADD(ms,-3,DATEADD(dd,1,CONVERT(DATETIME,EOMONTH(GETDATE()))))
2015-11-30 23:59:59.997
November 25, 2015 at 8:08 am
Joseph M. Steinbrunner (11/25/2015)
...did you notice that the EOMONTH returns a DATE instead of what the tooltip reads (DATETIME)?
Good point. Microsoft states the Return Type as DATE (https://msdn.microsoft.com/en-us/library/hh213020.aspx) but indeed, my SQL 2012 Intellisense says "Returns DATETIME".
March 30, 2017 at 3:45 pm
Can you please elaborate onQ1? Based on the chart at the top, the shortest a DATETIMEOFFSET can be is 8 and the longest aDATETIME2 can be is 8 so “AT BEST” they are equal.
April 2, 2017 at 7:20 pm
thisisfutile - Wednesday, November 25, 2015 7:49 AMI have a question for the community. What's the reasoning that DATE fields can't simply have a number added to them without the use of the DATEADD function? I assume it comes down to implicit conversions? This arithmetic always was (and still is) possible with DATETIME fields.I'm a late comer to SQL 2008 and later versions, so my introduction to DATE and TIME occurred recently. I was quite surprised to see all of my old scripts fail when I simply wanted to add a +1 to a DATE field and received "Operand type clash: date is incompatible with int". I then discovered that DATEADD was necessary. To me, it's much easier to read and type "MyDateField + 1".
I realize this question is 2 years old but thought I'd provide my thoughts on it. It's either because MS was in a hurry to release the new datatypes and didn't understand the value of direct date math or... hmmm... nah.... that's gotta be it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2017 at 7:49 pm
Just a couple of personal notes not covered in the article...
I certainly can't speak for anyone else but I find the ISDATE() function to have the same problems as the ISNUMERIC() function. For example, SELECT ISNUMERIC(',') will return a "1" because SELECT CONVERT(MONEY,',') will return a "0.00". For a similar reason, SELECT ISDATE('JAN1753') will return a "1" even it was really meant to be a user login (no spaces) for someone. Another example is (even though DATE can handle it), SELECT ISDATE('1753-01-01') will correctly return a "1" but will incorrectly SELECT ISDATE('1752-01-01') return a "0"... or so it would seem until you read the fine print. It only works with strings that can be converted to a DATETIME data type.
Don't get me wrong... I don't want anyone at MS to make a change there. It just takes a little knowledge as to what it can and cannot do.
As for EOMONTH, I avoid it like the plague even for DATE data types. You just don't know when someone is going to change the data type of a column to make it more resolute. I always use the form (for months in this example) of WHERE someDTcolumn >= {first of month} AND someDTcolumn < {first of NEXT month} and it has saved my hinny many times.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2017 at 3:28 pm
Jeff Moden - Sunday, April 2, 2017 7:20 PMthisisfutile - Wednesday, November 25, 2015 7:49 AMI have a question for the community. What's the reasoning that DATE fields can't simply have a number added to them without the use of the DATEADD function? I assume it comes down to implicit conversions? This arithmetic always was (and still is) possible with DATETIME fields.I'm a late comer to SQL 2008 and later versions, so my introduction to DATE and TIME occurred recently. I was quite surprised to see all of my old scripts fail when I simply wanted to add a +1 to a DATE field and received "Operand type clash: date is incompatible with int". I then discovered that DATEADD was necessary. To me, it's much easier to read and type "MyDateField + 1".I realize this question is 2 years old but thought I'd provide my thoughts on it. It's either because MS was in a hurry to release the new datatypes and didn't understand the value of direct date math or... hmmm... nah.... that's gotta be it. 😉
I literally, laughed out loud reading this. I'm glad I'm not the only one who felt it was simply oversight.
April 12, 2017 at 3:53 pm
thisisfutile - Wednesday, April 12, 2017 3:28 PMJeff Moden - Sunday, April 2, 2017 7:20 PMthisisfutile - Wednesday, November 25, 2015 7:49 AMI have a question for the community. What's the reasoning that DATE fields can't simply have a number added to them without the use of the DATEADD function? I assume it comes down to implicit conversions? This arithmetic always was (and still is) possible with DATETIME fields.I'm a late comer to SQL 2008 and later versions, so my introduction to DATE and TIME occurred recently. I was quite surprised to see all of my old scripts fail when I simply wanted to add a +1 to a DATE field and received "Operand type clash: date is incompatible with int". I then discovered that DATEADD was necessary. To me, it's much easier to read and type "MyDateField + 1".I realize this question is 2 years old but thought I'd provide my thoughts on it. It's either because MS was in a hurry to release the new datatypes and didn't understand the value of direct date math or... hmmm... nah.... that's gotta be it. 😉
I literally, laughed out loud reading this. I'm glad I'm not the only one who felt it was simply oversight.
Oooooo... I don't think it was an oversight... I think it was a bit of ignorance. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 13, 2017 at 2:18 pm
Jeff Moden - Wednesday, April 12, 2017 3:53 PMthisisfutile - Wednesday, April 12, 2017 3:28 PMJeff Moden - Sunday, April 2, 2017 7:20 PMthisisfutile - Wednesday, November 25, 2015 7:49 AMI have a question for the community. What's the reasoning that DATE fields can't simply have a number added to them without the use of the DATEADD function? I assume it comes down to implicit conversions? This arithmetic always was (and still is) possible with DATETIME fields.I'm a late comer to SQL 2008 and later versions, so my introduction to DATE and TIME occurred recently. I was quite surprised to see all of my old scripts fail when I simply wanted to add a +1 to a DATE field and received "Operand type clash: date is incompatible with int". I then discovered that DATEADD was necessary. To me, it's much easier to read and type "MyDateField + 1".I realize this question is 2 years old but thought I'd provide my thoughts on it. It's either because MS was in a hurry to release the new datatypes and didn't understand the value of direct date math or... hmmm... nah.... that's gotta be it. 😉
I literally, laughed out loud reading this. I'm glad I'm not the only one who felt it was simply oversight.
Oooooo... I don't think it was an oversight... I think it was a bit of ignorance. 😉
:laugh:
September 27, 2017 at 7:52 am
I created this list when I want to remember the formats:
select 100 'Convert_style',convert(varchar,GETDATE(),100) 'Output', 'MMM dd yyyy hh:mm/M' 'format', 'datetime' 'Type' union
select 101 'Convert_style',convert(varchar,GETDATE(),101) 'Output', 'mm/dd/yyyy' 'format', 'date' 'Type' union
select 102,convert(varchar,GETDATE(),102), 'yyyy.mm.dd', 'date' union
select 103,convert(varchar,GETDATE(),103), 'dd/mm/yyyy', 'date' union
select 104,convert(varchar,GETDATE(),104), 'dd.mm.yyyy', 'date' union
select 105,convert(varchar,GETDATE(),105), 'dd-mm-yyyy', 'date' union
select 106,convert(varchar,GETDATE(),106), 'dd MMM yyyy', 'date' union
select 107,convert(varchar,GETDATE(),107), 'MMM dd, yyyy', 'date' union
select 108,convert(varchar,GETDATE(),108), 'HH:mm:ss', 'time' union -- NN = minutes
select 109,convert(varchar,GETDATE(),109), 'MMM dd yyyy h:mm:ss:000/M ', 'datetime' union
select 110,convert(varchar,GETDATE(),110), 'mm-dd-yyyy', 'date' union
select 111,convert(varchar,GETDATE(),111), 'yyyy/mm/dd', 'date' union
select 112,convert(varchar,GETDATE(),112), 'yyyymmdd', 'date' union
select 113,convert(varchar,GETDATE(),113), 'dd MMM yyyy HH:mm:ss.000', 'datetime' union
select 114,convert(varchar,GETDATE(),114), 'hh:mm:ss.hhh', 'time' union
select 120,convert(varchar,GETDATE(),120), 'yyyy-mm-dd HH:mm:ss', 'datetime' union
select 121,convert(varchar,GETDATE(),121), 'yyyy-mm-dd HH:mm:ss.000', 'datetime' union
select 126,convert(varchar,GETDATE(),126),'yyyy-mm-ddTHH:mm:ss.000','datetime' union
select 127,convert(varchar,GETDATE(),127),'yyyy-mm-ddTHH:mm:ss.000','datetime' union
select 130,convert(varchar,GETDATE(),130),'different date + hh:mm:ss:00','datetime' union
select 131,convert(varchar,GETDATE(),131),'different date + hh:mm:ss:000/M','datetime'
412-977-3526 call/text
September 27, 2017 at 1:04 pm
Jeff Moden - Sunday, April 2, 2017 7:49 PMJust a couple of personal notes not covered in the article...I certainly can't speak for anyone else but I find the ISDATE() function to have the same problems as the ISNUMERIC() function. For example, SELECT ISNUMERIC(',') will return a "1" because SELECT CONVERT(MONEY,',') will return a "0.00". For a similar reason, SELECT ISDATE('JAN1753') will return a "1" even it was really meant to be a user login (no spaces) for someone. Another example is (even though DATE can handle it), SELECT ISDATE('1753-01-01') will correctly return a "1" but will incorrectly SELECT ISDATE('1752-01-01') return a "0"... or so it would seem until you read the fine print. It only works with strings that can be converted to a DATETIME data type.
Don't get me wrong... I don't want anyone at MS to make a change there. It just takes a little knowledge as to what it can and cannot do.
As for EOMONTH, I avoid it like the plague even for DATE data types. You just don't know when someone is going to change the data type of a column to make it more resolute. I always use the form (for months in this example) of WHERE someDTcolumn >= {first of month} AND someDTcolumn < {first of NEXT month} and it has saved my hinny many times.
Thanks for pointing out problems with isDate and isNumeric. I no longer use either. When I first encountered isDate, I naturally thought that a string that returned a 1 could be cast as a date. As you point out that is not the case. Equally troubling is isDate('2010') that returns a 1 and does cast to date(1/1/2010). A CLR is a better solution when date validation is required.
October 1, 2017 at 6:54 pm
billh-508166 - Wednesday, September 27, 2017 1:04 PMA CLR is a better solution when date validation is required.
Why would you think that?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply