asked on
T-SQL: Changing DATETIME into DATE and "NULL" Where '1900-01-01 00:00:00.000'
Hello:
I'm trying to return the SQL date time value of '1900-01-01 00:00:00.000' as NULL. I'm using the code below. But, instead, it's returning 01/01/1900.
True, I do want this date format of mm/dd/yyyy, if the date is a "real" date and not 01/01/1900. But, I want the date returned as NULL, if it is 01/01/1900.
What's wrong with my syntax?
Thanks!
John
I'm trying to return the SQL date time value of '1900-01-01 00:00:00.000' as NULL. I'm using the code below. But, instead, it's returning 01/01/1900.
True, I do want this date format of mm/dd/yyyy, if the date is a "real" date and not 01/01/1900. But, I want the date returned as NULL, if it is 01/01/1900.
What's wrong with my syntax?
Thanks!
ISNULL(CONVERT(VARCHAR(10),RM20101.DUEDATE,101),'') as [DUE DATE]
John
Microsoft SQL Server 2008SQL
Last Comment
PortletPaul
ASKER
When there is no date, for this datetime field, SQL returns '1900-01-01 00:00:00.000'. This field is a "not null datetime" field.
I found the means through that syntax of changing it to 01/01/1900. But, I need it to read as NULL. Otherwise, if it's a real date, I need it to read as mm/dd/yyyy.
And, I had this syntax typed up perfectly about an hour ago, until I closed out of SQL Studio and forgot to save the syntax.
So, yes, there is a way to do this.
I found the means through that syntax of changing it to 01/01/1900. But, I need it to read as NULL. Otherwise, if it's a real date, I need it to read as mm/dd/yyyy.
And, I had this syntax typed up perfectly about an hour ago, until I closed out of SQL Studio and forgot to save the syntax.
So, yes, there is a way to do this.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you, Shaun! You got me to the solution, as follows:
John
CASE CAST(RM20101.DUEDATE as DATE) WHEN '01/01/1900' then NULL else convert(varchar(10), RM20101.DUEDATE, 101)
END as [DUE DATE],
John
when '19000101' ...
YYYYMMDD Is the safest way to represent a date in your tsql code.
Dont rely on mm/dd/yyyy which can be confused with dd/mm/yyyy
YYYYMMDD Is the safest way to represent a date in your tsql code.
Dont rely on mm/dd/yyyy which can be confused with dd/mm/yyyy
Have you tested the column to make sure that the value is NULL and not the epoch date?
Kent