Solved

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

Posted on 2016-08-03
5
29 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:Kdo
Comment Utility
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
Comment Utility
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 25

Accepted Solution

by:
Shaun Kline earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now