Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2016-08-03
5
80 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 33
Query Help - MSSQL - Averages 5 30
T-SQL: New to using transactions 9 46
Microsoft Access Delete all Records from table but Max 2 23
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

792 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