Solved

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

Posted on 2015-01-28
4
187 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:SimonAdept
SimonAdept 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 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

18 Experts available now in Live!

Get 1:1 Help Now