Solved

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

912 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now