Post reply

Show Blanks as Blanks or NULL in Date Field

  • November 29, 2012 at 3:46 am

    #267511

    Hi All,

    I have a table which stores Dates as Text.I want to Convert that text into Date when selecting the data.I cannot change the parent table as i have no control on it.

    Textsample is the table i have to run select query on but i want the resultant column "Date" in Date Format.

    When i convert it into Date Format the blank value are shown as 1900-01-01 because blank value are treated as Zero value.

    Is there any method i can show blanks in the result as well?

    Here the sampe query

    CREATE TABLE #TextSample ( Event VARCHAR(50), Date VARCHAR(8))

    INSERTINTO #TextSample

    SELECT'Event1' Event, '12/12/12' Date UNION ALL

    SELECT'Event2', '01/12/12' UNION ALL

    SELECT'Event3', '' UNION ALL

    SELECT'Event4', '20/11/12' UNION ALL

    SELECT'Event5', '' UNION ALL

    SELECT'Event6', ''

    select * from #TextSample

    CREATE TABLE #DateSample ( Event VARCHAR(50), Date Date)

    INSERTINTO #DateSample

    SELECT

    #Textsample.Event

    ,CONVERT(DATE, #Textsample.Date, 03) AS "Date"

    from #TextSample

    Select * from #DateSample

    DROP TABLE #TextSample

    DROP TABLE #DateSample

    Regards

    santa326

  • November 29, 2012 at 3:49 am

    #1563652

    Hi,

    This will turn those dates to NULLs

    CREATE TABLE #TextSample ( Event VARCHAR(50), Date VARCHAR(8))

    INSERTINTO #TextSample

    SELECT'Event1' Event, '12/12/12' Date UNION ALL

    SELECT'Event2', '01/12/12' UNION ALL

    SELECT'Event3', '' UNION ALL

    SELECT'Event4', '20/11/12' UNION ALL

    SELECT'Event5', '' UNION ALL

    SELECT'Event6', ''

    select * from #TextSample

    CREATE TABLE #DateSample ( Event VARCHAR(50), Date Date)

    INSERTINTO #DateSample

    SELECT

    #Textsample.Event

    ,CASE

    WHEN #Textsample.Date = '' THEN NULL

    ELSE CONVERT(DATE, #Textsample.Date, 03)

    END AS "Date"

    from #TextSample

    Select * from #DateSample

    DROP TABLE #TextSample

    DROP TABLE #DateSample


    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • November 29, 2012 at 4:03 am

    #1563662

    Hi,

    thanks for the reply.It worked

    Thanks

  • November 29, 2012 at 4:07 am

    #1563664

    Hi,

    No worries, if you want to show the 1900 dates as a blank you could always convert the date back to a varchar using the appropriate style and then do an ISNULL but that should be left to the presentation layer really.

    Thanks,

    Simon


    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • November 29, 2012 at 4:15 am

    #1563666

    You could also use NULLIF (which is wrapper around CASE WHEN...)

    SELECT

    #Textsample.Event, CONVERT(DATE,NULLIF(#Textsample.Date,''),3)

    from #TextSample

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • November 29, 2012 at 4:17 am

    #1563667

    Thanks! I'd never seen that before! That'll save me several lines of code next time.


    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply