?
Solved

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

Posted on 2015-01-28
4
Medium Priority
?
206 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 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 1000 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 66

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

752 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