April 12, 2017 at 9:54 pm
Comments posted to this topic are about the item Nuance of datetime data type in SQL Server
April 13, 2017 at 2:19 am
Thanks for sharing this one Leo. It is definitely a "Nuance" but it could just as easily become a "Nuisance". I have dealt with hunting down data problems in the past I have not previously encountered this. I hope I don't see it in the wild.
Just when we thought that the "year 2000 problem" had gone away, something like this reminds us that having a "beginning of time" can cause problems as well! 😉
Tom Gillies LinkedIn Profilewww.DuhallowGreyGeek.com[/url]
April 13, 2017 at 3:29 am
As pedantic as this is.....
"I can see only one way to do this"
IF len (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0
I suspect it may well be less efficient, but wouldn't this also work:
IF cast(cast(cast(@datetimevar as date) as datetime) as int) = 0
April 13, 2017 at 4:10 am
You should use an INLINE TABLE VALUE fuction instead of a scalar function - this would save a lot of time when you import many datasets
CREATE FUNCTION dbo.f_xxx(@datetimevar varchar(50))
RETURNS TABLE AS RETURN
SELECT CASE WHEN len (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0 THEN 1 ELSE 0 END as bitvar
;
SELECT t.*, f.bitvar
FROM tbl AS t
CROSS APPLY dbo.f_xxx(t.datetimevar) AS f
God is real, unless declared integer.
April 13, 2017 at 5:22 am
I don't agree at thinking that when a time value is inserted and the default is 1900-01-01 would be "wrong". You inserted a time into a type that is for date AND time. Isn't this why a time datatype exist? Maybe I missed the point in the discussion and that's on me.
Also, why not switch to datetime2 and the default becomes a year you for sure know is not a valid date, unlike 1900-01-01 as you mentioned could be a real date in certain scenarios.
April 13, 2017 at 6:10 am
SQLian - Thursday, April 13, 2017 3:29 AMAs pedantic as this is....."I can see only one way to do this"
IF len (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0I suspect it may well be less efficient, but wouldn't this also work:
IF cast(cast(cast(@datetimevar as date) as datetime) as int) = 0
One way mean parsing. There are many ways to parse and your way will work as well
April 13, 2017 at 6:14 am
Leo Peysakhovich - Thursday, April 13, 2017 6:10 AMSQLian - Thursday, April 13, 2017 3:29 AMAs pedantic as this is....."I can see only one way to do this"
IF len (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0I suspect it may well be less efficient, but wouldn't this also work:
IF cast(cast(cast(@datetimevar as date) as datetime) as int) = 0One way mean parsing. There are many ways to parse and your way will work as well
By the way it is not working because both dates return 0 but the first date is correct while the second one presented bad data.
select cast(cast(cast('1900 10:00' as date) as datetime) as int)
select cast(cast(cast('10:00' as date) as datetime) as int)
April 13, 2017 at 6:23 am
louie1487 78804 - Thursday, April 13, 2017 5:22 AMI don't agree at thinking that when a time value is inserted and the default is 1900-01-01 would be "wrong". You inserted a time into a type that is for date AND time. Isn't this why a time datatype exist? Maybe I missed the point in the discussion and that's on me.Also, why not switch to datetime2 and the default becomes a year you for sure know is not a valid date, unlike 1900-01-01 as you mentioned could be a real date in certain scenarios.
Datetime2 has the same behavior.
declare @dt datetime2 = '10:00', @dt1 datetime2 = '1/1/1900'; select @dt, @dt1;
But the main point of the article that you accidently may have result while data is bad and this bad data will be in system unnoticed.
April 13, 2017 at 6:38 am
louie1487 78804 - Thursday, April 13, 2017 5:22 AMI don't agree at thinking that when a time value is inserted and the default is 1900-01-01 would be "wrong". You inserted a time into a type that is for date AND time. Isn't this why a time datatype exist? Maybe I missed the point in the discussion and that's on me.Also, why not switch to datetime2 and the default becomes a year you for sure know is not a valid date, unlike 1900-01-01 as you mentioned could be a real date in certain scenarios.
It's wrong because assuming a value not provided is wrong. One common use-case where this makes datetime data types not fit for purpose is in genealogy.
For example, if I were to tell you that I was born in 1973, would you assume that I was born on January 1, 1973? What if I were to say I was born some time in the 1970s? Does assuming 1970 exactly make sense? Yet it's common in genealogy to know someone's birth only as far as the decade. Sometimes you even know the month and day, but not the exact year.
This is a side-effect of datetime data being stored atomically but represented non-atomically. Because it's stored atomically, it can only either be NULL or have a single value. But it's represented as six different intervals, each of which in real world situations may or may not be known.
April 13, 2017 at 12:16 pm
I'm keeping this one in my briefcase because I'm dealing with a lot of legacy data out of multiple systems. I keep finding dirty data all over the place and have no doubt I'll find this one too.
Thanks for sparing us some pain!
April 13, 2017 at 1:46 pm
SQLian - Thursday, April 13, 2017 3:29 AMAs pedantic as this is....."I can see only one way to do this"
IF len (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0I suspect it may well be less efficient, but wouldn't this also work:
IF cast(cast(cast(@datetimevar as date) as datetime) as int) = 0
You can reduce it even more.IF cast(@datetimevar as date) = '1900'
April 13, 2017 at 1:54 pm
Oh good grief. Whenever I hear people talking about this subject like this, it drives me absolutely *nuts*. I want to get out a hammer and start breaking fingers.
The premise of the article is utterly wrong. This is not a nuance, it is flat NORMAL. It is not something uncommon, it happens *everywhere*.
It is a fundamental lack of comprehension of the datetime datatype. Thinking like this has caused more bugs over the last 70 years than the NULL. It is thinking like this that gave the world Y2K.
Please get this through your thick heads--
EVERY datetime has a date. EVERY datetime has a time.
There are no exceptions to this, none.
When you get date data in, immediately store it in a datetime and *leave it there*. Do not allow users to enter invalid dates, use a calendar control.
If you convert a datetime to text for something other than display, you are doing something WRONG. By WRONG, I mean completely bat-droppings crazy.
alter function dbo.udfTimeOnly ( @datetimevar datetime )
returns bit
as
begin
declare @bitvar bit;
IF cast(@datetimevar as date) = cast(0 as datetime)
--date is zero date--assume invalid
set @bitvar = 1;
ELSE
set @bitvar = 0;
return @bitvar;
end;
As a another ranting aside, to show foobar data, I highly advise using something other than three exes, you know, these things: x.
April 14, 2017 at 5:30 am
sknox - Thursday, April 13, 2017 6:38 AMlouie1487 78804 - Thursday, April 13, 2017 5:22 AMI don't agree at thinking that when a time value is inserted and the default is 1900-01-01 would be "wrong". You inserted a time into a type that is for date AND time. Isn't this why a time datatype exist? Maybe I missed the point in the discussion and that's on me.Also, why not switch to datetime2 and the default becomes a year you for sure know is not a valid date, unlike 1900-01-01 as you mentioned could be a real date in certain scenarios.
It's wrong because assuming a value not provided is wrong. One common use-case where this makes datetime data types not fit for purpose is in genealogy.
For example, if I were to tell you that I was born in 1973, would you assume that I was born on January 1, 1973? What if I were to say I was born some time in the 1970s? Does assuming 1970 exactly make sense? Yet it's common in genealogy to know someone's birth only as far as the decade. Sometimes you even know the month and day, but not the exact year.
This is a side-effect of datetime data being stored atomically but represented non-atomically. Because it's stored atomically, it can only either be NULL or have a single value. But it's represented as six different intervals, each of which in real world situations may or may not be known.
The datetime datatype is assuming a "default" when, in this case, only the time is provided. That's just the way it is, and according to me this makes sense. Otherwise you should only allow full date and time information (until the precision of the chosen datetime datatype) to be used to fill a datetime. Because otherwise you can also asked why '2017-04-14' is defaulted to '2017-04-14 00:00:000', even though it should be '2017-04-14 01:01:001'.
You are correct is stating that a datetime is not fit for purpose in genealogy, so the use of a datetime there doesn't make any sense there. In a correct data model design you would use multiple fields (year, month, day,...) to store this information. 1973 is not a date, it is a year, so why would you even want to store it in a datetime column?
April 14, 2017 at 7:29 am
SQLian - Thursday, April 13, 2017 3:29 AMAs pedantic as this is....."I can see only one way to do this"
IF len (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0I suspect it may well be less efficient, but wouldn't this also work:
IF cast(cast(cast(@datetimevar as date) as datetime) as int) = 0
Not for the purpose intended in this case. Leo's code is designed to check to see if the date is included in the original character based data. If not, it's a problem. If it is, even for the 1900-01-01 date (he did say he needed to allow for that), then it correctly passes the test using his code. It won't, in this case, for yours because you're rejecting the 1900-01-01 date.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2017 at 7:32 am
Mighty - Friday, April 14, 2017 5:30 AMThe datetime datatype is assuming a "default" when, in this case, only the time is provided. That's just the way it is, and according to me this makes sense.
Heh... and just the way I like it. I've worked on other systems that try to protect the programmer/user and, while the intention is good, it defeats building special purpose code that can't withstand the automatic protection.
--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