Solved

Converting int column to datetime with null values replaced with N/A

Posted on 2015-01-28
4
193 Views
Last Modified: 2015-02-02
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 ?

Many Thanks

--before

CASE WHEN LastSubmittalDateKey = 19000101 THEN NULL
     ELSE CONVERT(datetime,CONVERT(varchar, FACCLastSubmittalDateKey))
     END AS [Last application submission date orginal]


--AFTER

ISNULL(CAST(CASE WHEN LastSubmittalDateKey = 19000101 THEN NULL
            ELSE CONVERT(datetime,CONVERT(varchar,LastSubmittalDateKey))
             END AS VARCHAR),'N/A')AS [Last application submission date]
0
Comment
Question by:gvamsimba
  • 2
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 40575302
>NULL values to be replaced with N/A
That's because you changed the date column to a varchar, and varchar's don't have a year, month, and date component to sort on.  Recommend telling your users to live with the NULL values, or find a way in the Excel doc to show NULL's as blank if that's too confusing.
0
 
LVL 18

Assisted Solution

by:Simon
Simon earned 250 total points
ID: 40575304
If there are mixed datatypes in the column (dates and strings) Excel will not be able to group or sort the rows using dates.

You might want to leave the original column as it was and have the column that includes the 'N/A' as an extra 'presentation' column in the view, so you can sort/filter/group by dates but still display the values that the end users want to see.

Either that or leave your view as is, and tell the users to stick... a calculated column next to the query results that does =if(ISBLANK(LastSubmittalDateKey),"N/A",LastSubmittalDateKey).

-- replace LastSubmittalDateKey in the above formula with the cell reference.
0
 

Author Closing Comment

by:gvamsimba
ID: 40583662
Yes, they cannot expect text in date fields...Agreed...
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40583891
Another possibility is to pass two columns with these values:  One as datetime with NULLs and sort on that one, and one as varchar's with 'N/A's' and display that one.  That'll work for reports, but won't work once it's in an interactive view like Excel, and the user wants to sort on that column.

Thanks for the split, good luck.  
Jim
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
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…

777 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