Null Date
-
Hello,
I have a table with a date field and I want to keep that field to NULL until I have some real data to record in it.
It happens that when I update some record of that table and I use an empty string to fill that column, sqlserver puts this date "01-01-1900" in that column.
(I use that empty string to avoid check in the UPDATE statement if the date is NULL or not)
How can I avoid this and keep that date field NULL or empty?
Thank you,
Augusto
-
Dates cannot contain 'empty' string, as you found out sql will set id to '1900-01-01'. If your input is not datetime (eg varchar) then use NULLIF, eg
DECLARE @string varchar(10)
SET @string = ''
SELECT @string,NULLIF(@string,'')
Far away is close at hand in the images of elsewhere.
Anon. -
To add to David, 01/01/1900 is SQL Server's reference date. All other dates are calculated as days before or after that date. So when you want to avoid dealing with NULL logic in your queries, I guess you have to deal with this date.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
SQL converts an empty string ('') as 0 and date 0 corresponds to 1900-01-01 , date 1 = 1900-01-02 etc. The following statements demonstrate this.
SELECT CONVERT(DATETIME, '') AS [Date ''],CONVERT(INT, '') AS [INT '']
Date '' INT ''
--------------------------- -----------
1900-01-01 00:00:00.000 0
SELECT CONVERT(DATETIME, 0) AS [Date 0], CONVERT(DATETIME, 1) AS [Date 1], CONVERT(INT, GETDATE()) AS [Current Date Value]
Date 0 Date 1 Current Date Value
--------------------------- --------------------------- ------------------
1900-01-01 00:00:00.000 1900-01-02 00:00:00.000 38012
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply