Solved

T-SQL: Changing DATETIME into DATE and "NULL" Where '1900-01-01 00:00:00.000'

Posted on 2016-08-03
5
105 Views
Last Modified: 2016-08-03
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
0
Comment
Question by:John Ellis
5 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 41741211
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
0
 

Author Comment

by:John Ellis
ID: 41741231
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.
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 41741239
One possible solution:
declare @test datetime

set @test = '1900-01-01 00:00:00.001'

select case when @test = cast('1900-01-01' as datetime) then NULL else convert(varchar(10), @test, 101) end

set @test = GETDATE()

select case when @test = cast('1900-01-01' as datetime) then NULL else convert(varchar(10), @test, 101) end

Open in new window

0
 

Author Comment

by:John Ellis
ID: 41741400
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41741422
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
1

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question