Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-08-03
5
Medium Priority
?
203 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
[X]
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
5 Comments
 
LVL 46

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 27

Accepted Solution

by:
Shaun Kline earned 2000 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 49

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 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