Link to home
Create AccountLog in
Avatar of John Ellis
John Ellis

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!

ISNULL(CONVERT(VARCHAR(10),RM20101.DUEDATE,101),'') as [DUE DATE]

Open in new window


John
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

It's entirely possible that the stored date is zero (epoch date + zero) and not NULL.

Have you tested the column to make sure that the value is NULL and not the epoch date?


Kent
Avatar of John Ellis
John Ellis

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.
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you, Shaun!  You got me to the solution, as follows:

CASE CAST(RM20101.DUEDATE as DATE) WHEN '01/01/1900' then NULL else convert(varchar(10), RM20101.DUEDATE, 101) 
END as [DUE DATE],

Open in new window


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