user1837575 user1837575 - 1 year ago 56
SQL Question

How to convert date, but return nothing if Null?

I have a datetime field that is store in the database looking like this: 2016-06-30 00:00:00.000

I am being asked to convert that format to dd/mm/yyyy

I can do this with

Convert(varchar,PRICE_TAB.F137,103) as 'SalesPriceStartDateGood',

This column contains NULLS though, and where it contains NULLS I want to return nothing. This is being exported to CSV so I want it to come across as ,, for that spot in the file.

How do I write the query to say convert to this format, unless null, then give me nothing?

Answer Source

I tried it without coalesce and the result seems like what you want :

declare @test1 datetime = getdate()
declare @test2 datetime = null

select convert(varchar, @test1, 103) as test1, 
       convert(varchar, @test2, 103) as test2

this returns
28/06/2016 null

if you need an empty string than just do this

select convert(varchar, @test1, 103) as test1, 
       isnull(convert(varchar, @test2, 103), '') as test2

this returns
28/06/2016 ''

for your table it will be this :

isnull(Convert(varchar,PRICE_TAB.F137,103), '') as SalesPriceStartDateGood'  
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download