Solved

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

Posted on 2015-01-28
4
197 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
database level memory cache..? 8 37
SQL Server - Getting the most recent engagement for each contact 9 45
union query column need default text 2 20
TSQL Challenge... 7 44
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

733 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