Post reply

Identifying NULL Dates

  • May 12, 2005 at 11:15 am

    #164438

    I'm writing a TSQL procedure using a CASE statement to display a NULL date represented as '1900-01-01 00:00:00.000' as a '' string but having no success. 

    I've tried IS NULL, direct comparisons, using DATEDIFF and comparing against GETDATE(), and CASTing the value to nvarchar and comparing.  Any help would be appreciated.

     

     

  • May 12, 2005 at 11:27 am

    #558500

    this works :

    Declare @MyDate as datetime

    set @MyDate = null

    select isnull(@Mydate, '1900-01-01 00:00:00.000')

  • May 12, 2005 at 11:37 am

    #558506

    Thanks.  I found another solution using ISDATE which will return a 0 for NULLs and 1 for valid dates.  Then, I also discovered that unless I CAST the date variable as a varchar displaying it as an empty string '' in the CASE statement will also display it as '1900-01-01 00:00:00.000'. 

  • May 12, 2005 at 11:40 am

    #558509

    Yes there is no need to have a CASE for your TSQL

    Just

    SELECT ISNULL(MYDATE,'1900-01-01 00:00:00.000') FROM MYTABLE, will give you the desired output.

    Prasad Bhogadi
    www.inforaise.com

  • May 13, 2005 at 10:46 am

    #558788

    This can also be accomplished by simply:

    SELECT ISNULL(MYDATE,'') FROM MYTABLE

    as long as MYDATE is datetime.

    Kemp

  • May 13, 2005 at 12:07 pm

    #558811

    I see that some would fall into the same situation I was in.  My original post indicated I had problems because I couldn't seem to weed out the NULL value on my displays.  Originally is used all the select statements for selecting NULL date values but kept getting the NULL date being displayed.

    CASE WHEN datefield IS NULL THEN ''

    will find the NULL value and display a NULL value.  In the CASE statement the '' translates to a NULL for the display.  So I was finding the NULL in the case statement and then displaying a NULL.  I reasoned then that the CASE statement was not working but I was wrong.  It was working but the '' displayed the date NULL value.  I resolved this by using:

    CASE WHEN datefield IS NULL THEN 'N/A'

    I could as easily used a space.

     

     

     

  • May 13, 2005 at 9:35 pm

    #558879

    scking,

    I'm curious... what is your objection to using ISNULL or it's big brother, COALESCE?  Yes, CASE works but it's probably not a fast as ISNULL...  your last CASE example could easily be written as...

    ISNULL(datefield,'N/A')

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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