Converting int column to datetime with null values replaced with N/A
Posted on 2015-01-28
Hi I have a column named LastSubmittalDateKey in my table with datatype int. Users normally connect to this VIEW via Excel and they want this column with datetime so that they can filter it easily based on year, month and day.
Now the before script is doing exactly what I wanted. but now they want the NULL values to be replaced with N/A and I changed the report to AFTER as you can see below but the problem is when
i connect to excel this column is displaying as text and not allowing me to filter on year, month and day as before.
can anyone please let me know what the issue is here and how to get my desired result ?
CASE WHEN LastSubmittalDateKey = 19000101 THEN NULL
ELSE CONVERT(datetime,CONVERT(varchar, FACCLastSubmittalDateKey))
END AS [Last application submission date orginal]
ISNULL(CAST(CASE WHEN LastSubmittalDateKey = 19000101 THEN NULL
END AS VARCHAR),'N/A')AS [Last application submission date]