April 17, 2009 at 12:52 pm
I knew that a datetime field defaulted to '1/1/1900' when the field contains blanks, but it's looking like the 'inverse' is also true, which is causing problems.
that is, when I specifically insert a value of 1/1/1900 into a datetime (or smalldatetime) field, it's treated as blank. I figure I must be missing something, but consider the following:
create table testdate2 (datefield smalldatetime,thekey int identity(1,1),descr varchar(20))
insert testdate2 (datefield,descr) values('','blank')
insert testdate2 (datefield,descr) values('1/1/1900','1/1/1900')
insert testdate2 ( descr) values('null')
select * from testdate2
select * from testdate2 where datefield is null -- OK
select * from testdate2 where datefield ='1/1/1900' -- OK
select * from testdate2 where datefield ='' -- no, one of them really should have '1/1/1900'
select * from testdate2 where datefield ='' and datefield ='1/1/1900' -- ?
Like I said, I must be missing something because I can't find this problem addressed anywhere.
tia.....
April 17, 2009 at 1:15 pm
There is no problem. SQL Server is working as designed and as documented.
When you cast a zero length string to datatype of datetime, it casts it to the SQL Server zero date, 1900-01-01 00:00:00.000
April 17, 2009 at 1:30 pm
I am not seeing any problems with this - what are the results you are getting?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 17, 2009 at 2:31 pm
I'm specifically inserting a date value of '1/1/1900'. Not blank; not zero length; 1/1/1900. (the results are the same for datetime as well as smalldatetime, incidentally.)
It's getting treated as blank. This can't be right.
April 17, 2009 at 2:49 pm
Not sure I follow you - with your example script, the only date column that is blank is the one where you don't insert anything:
insert #testdate2 ( descr) values('null')
The date column allows null and does not have a default, so that insert puts a null into the date column.
The other two rows have the date as 01/01/1900 - just as you inserted it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 17, 2009 at 2:50 pm
I think the confusion is that sql converts '' to 1/1/1900. If you need to use this date and also have "blank" try inserting null instead of ''. If this is coming from a UI (which is usually where this issue arises) add some logic on the front end that if your datepicker = '' then insert null.
In sql '' = '1/1/1900' when referring to a (small)datetime field. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2009 at 3:50 pm
I don't think I'm explaining this well.
I understand that SQL Server treats blanks as '1/1/1900', at least in display. What I don't understand is the reverse, why a specific insert of '1/1/1900' is treated as blanks. In as sense that's basically saying that 1/1/1900 is not a valid date. If you issue a delete to delete rows where the date is blank, you'll delete the row that was specifically inserted as 1/1/1900.
April 17, 2009 at 4:15 pm
d short (4/17/2009)
I don't think I'm explaining this well.I understand that SQL Server treats blanks as '1/1/1900', at least in display. What I don't understand is the reverse, why a specific insert of '1/1/1900' is treated as blanks. In as sense that's basically saying that 1/1/1900 is not a valid date. If you issue a delete to delete rows where the date is blank, you'll delete the row that was specifically inserted as 1/1/1900.
Not quite - when you issue the following statement:
DELETE FROM testdate WHERE datefield = '';
You are not deleting rows where the datefield is actually equal to blank (''). You are deleting rows where the datefield is equal to the conversion of the blank field to datetime datatype which is equal to 01/01/1900.
When you select from the table as in:
SELECT * FROM testdate WHERE datefield = '';
The first thing SQL Server does is converts the blank to a datetime value. Then it compares the converted value to the datefield and finds a match.
When you insert into the table - if you specify a value for the datefield that value will be inserted. If the value you specify is not a date - SQL Server attempts to convert to the appropriate datatype - and since a blank ('') converts to 0 which can be converted to 01/01/1900 that is the value that is inserted into the table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 20, 2009 at 9:29 am
thanks for the responses.
April 21, 2009 at 6:57 am
curious - what functionality are you adding in that needs to see 1/1/1900 as a valid date?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 23, 2009 at 11:33 am
We have individuals loaded into a DB with actual birthdates of 1/1/1900. (Granted, most of them aren't around anymore, afaik). There are also records loaded into the DB with a blank value. (Granted, these should be null, but it wasn't my idea.)
since it's a datetime column, SQL Server can't tell the difference between them.
April 23, 2009 at 11:41 am
Crazy. Wonder what those folks would think if they knew their birthdate was causing issues in your fancy database and you went to the internet to help resolve it?
🙂
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 2, 2015 at 12:41 pm
Can you provide an example?
I think I know what you're trying to say, though. Like this?
SELECT NULLIF(CAST('1/1/1900' AS datetime),'')
April 2, 2015 at 2:16 pm
ab5sr (4/2/2015)
Can you provide an example?I think I know what you're trying to say, though. Like this?
SELECT NULLIF(CAST('1/1/1900' AS datetime),'')
Even though this thread is 6 years old...
What is your question here? Not sure what you are trying to do there but the NULLIF is kind of pointless. The issue here is about implicit conversion. An empty string when cast to a datetime will convert to 1/1/1900.
To demonstrate you can reverse your dates here and it will still return NULL.
SELECT NULLIF(CAST('' AS datetime), '1/1/1900')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply