SQL Server null date 01/01/1900 possible issue - a question
-
SQL Server will return a null date as 01/01/1900 if one castes a null string to a date. Now the issue is that someone had the idea of populating dates with 01/01/1900 to signify a certain stage with a row(meaning that the date is not known but it is known that that stage has been reached). I only realised yesterday that this could cause an issue if a query is written where a null string is compared to one of these dates. Does anyone else have experience of this and any suggestions? I am currently thinking of changing these 01/01/1900 dates to 01/01/1901 to remedy this potential problem. Thanks
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
SQL Server will return a null date as 01/01/1900 if one castes a null string to a date. Now the issue is that someone had the idea of populating dates with 01/01/1900 to signify a certain stage with a row(meaning that the date is not known but it is known that that stage has been reached). I only realised yesterday that this could cause an issue if a query is written where a null string is compared to one of these dates. Does anyone else have experience of this and any suggestions? I am currently thinking of changing these 01/01/1900 dates to 01/01/1901 to remedy this potential problem. Thanks
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
Which version of SQL Server? I've just tried with SQL 2008, and casting a
varcharcontainingNullto eitherdatetime,smalldatetime,datetime2(0)ordatereturnsNullin all cases. http://www.sqlfiddle.com/#!3/d41d8/13023[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
SQL Server will return a null date as 01/01/1900 if one castes a null string to a date. Now the issue is that someone had the idea of populating dates with 01/01/1900 to signify a certain stage with a row(meaning that the date is not known but it is known that that stage has been reached). I only realised yesterday that this could cause an issue if a query is written where a null string is compared to one of these dates. Does anyone else have experience of this and any suggestions? I am currently thinking of changing these 01/01/1900 dates to 01/01/1901 to remedy this potential problem. Thanks
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
SQL Server will return a null date as 01/01/1900 if one castes a null string to a date. Now the issue is that someone had the idea of populating dates with 01/01/1900 to signify a certain stage with a row(meaning that the date is not known but it is known that that stage has been reached). I only realised yesterday that this could cause an issue if a query is written where a null string is compared to one of these dates. Does anyone else have experience of this and any suggestions? I am currently thinking of changing these 01/01/1900 dates to 01/01/1901 to remedy this potential problem. Thanks
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
GuyThiebaut wrote:
SQL Server will return a null date as 01/01/1900 if one castes a null string to a date.
I seriously doubt that. As a guess the data is actually an empty string.
jschell wrote:
the data is actually an empty string
Yes, that makes sense: http://www.sqlfiddle.com/#!3/d41d8/13027[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
GuyThiebaut wrote:
SQL Server will return a null date as 01/01/1900 if one castes a null string to a date.
I seriously doubt that. As a guess the data is actually an empty string.
Thanks. I will have to check this with regards to the data I have been looking at - however I think you have hit the proverbial nail on the head. I think I will go with changing the dates to something other than 01/01/1900 as I can see someone in the future doing an
isnullcomparison on an empty string and getting a match against these dates.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Thanks. I will have to check this with regards to the data I have been looking at - however I think you have hit the proverbial nail on the head. I think I will go with changing the dates to something other than 01/01/1900 as I can see someone in the future doing an
isnullcomparison on an empty string and getting a match against these dates.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
GuyThiebaut wrote:
as I can see someone in the future doing an
isnullcomparison on an empty string and getting a match against these dates.I don't understand that statement so just to be sure I will state that an empty string is not the same as a null value.
-
Which version of SQL Server? I've just tried with SQL 2008, and casting a
varcharcontainingNullto eitherdatetime,smalldatetime,datetime2(0)ordatereturnsNullin all cases. http://www.sqlfiddle.com/#!3/d41d8/13023[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Hello, this is not SQL Server Issue. This is related to the Connection Type you ar using (ADO.NET, OLE DB, ODBC...). for example: If you are using MS Access you will see: [Null] dates returns/saves [01/01/1900] [Bit] field returns [-1] or [1] Just a moment ago a friend asked me about the same problem, he is developing on Visual Studio .NET 2008 SP1 (I really did not checked his code, but he resolved his problem). Hope this help. Best regards.
-
Hello, this is not SQL Server Issue. This is related to the Connection Type you ar using (ADO.NET, OLE DB, ODBC...). for example: If you are using MS Access you will see: [Null] dates returns/saves [01/01/1900] [Bit] field returns [-1] or [1] Just a moment ago a friend asked me about the same problem, he is developing on Visual Studio .NET 2008 SP1 (I really did not checked his code, but he resolved his problem). Hope this help. Best regards.
