CODEWITHSUNDEEP
sqlsql-serverdatetimenull
Emily
Emily

Reputation: 71

Convert NULL datetime to Blank

I would like to convert a datetime field to varchar so I can show a blank or a message when the date is NULL. This is what I have so far:

select
isnull(us.Date,0) as USDate,
isnull(au.Date,0) as AUDate
from ustable us
left join autable au
on us.column=au.column

Right now this is showing: 

USDATE 
2014-10-24 10:29:07.450
1900-01-01 00:00:00.000

I would like to be able to make the "1900-01-01 00:00:00.000" varchar so I can write a message or show a true blank.

Upvotes: 7

Views: 59467

Answers (7)

raghavendra khatavkar
raghavendra khatavkar

Reputation: 1

select null USDate, NUll AUDate
from ustable us
left join autable au on us.column=au.column

It's work try it, it will show column value should be null.

Upvotes: 0

kttii
kttii

Reputation: 107

You can use the NVL() function ...

You can use the NVL function to convert an expression that evaluates to NULL to a value that you specify. The NVL function accepts two arguments: the first argument takes the name of the expression to be evaluated; the second argument specifies the value that the function returns when the first argument evaluates to NULL. If the first argument does not evaluate to NULL, the function returns the value of the first argument.

For example:

select
nvl(us.Date,'') as USDate,
nvl(au.Date,'') as AUDate
from ustable us
left join autable au
on us.column=au.column

Or put anything you want as the Default:

select
nvl(us.Date,'this was a NULL') as USDate,
nvl(au.Date,'this was a NULL') as AUDate
from ustable us
left join autable au
on us.column=au.column

Upvotes: 1

Lewis
Lewis

Reputation: 101

The simplest line of code I've found for what you're trying to accomplish is as follows.

ISNULL(CONVERT(VARCHAR(30),us.Date,121),'') as USDate,
ISNULL(CONVERT(VARCHAR(30),au.Date,121),'') as AUDate,

I have tested this and verified that it works.

Upvotes: 10

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

You can use Case AND CONVERT like this -

SELECT
CASE WHEN us.Date IS NULL THEN '' ELSE CONVERT(VARCHAR(30), us.Date, 121) END AS USDate,
CASE WHEN au.Date IS NULL THEN '' ELSE CONVERT(VARCHAR(30), au.Date, 121) END AS AUDate
FROM ustable us
left join autable au
on us.column=au.column

Upvotes: 7

Nicholas Carey
Nicholas Carey

Reputation: 74365

Given a column defined like this:

date_of_birth datetime null

You can simply say

select date_of_birth = coalesce( convert(varchar(32),date_of_birth) , '' )
from some_table_with_a_nullable_datetime_column

Upvotes: 1

Alex W
Alex W

Reputation: 38253

To do it in SQL:

Change your SELECT statement to incorporate a WHEN...THEN clause:

CASE us.Date 
  WHEN '1900-01-01 00:00:00.000' THEN ''
  WHEN NULL THEN ''
  ELSE us.Date
END as USDate,
CASE au.Date 
  WHEN '1900-01-01 00:00:00.000' THEN ''
  WHEN NULL THEN ''
  ELSE au.Date
END as AUDate

If you have front-end code, then you could just implement the functionality on the front-end without changing your SQL code.

Upvotes: 1

DeadZone
DeadZone

Reputation: 1690

Try something like this...

select
CONVERT(varchar(50), isnull(us.Date,0)) as USDate,
CONVERT(varchar(50), isnull(au.Date,0)) as AUDate
from ustable us
left join autable au
on us.column=au.column

You'll want to change the size of the varchar depending upon your date format. Also, CONVERT takes a third parameter that will allow you to change the format of your string.

Upvotes: 0

Related Questions