How do I remove nulls from case statements with date fields?

  • How do I remove nulls from case statements with date fields . For example in the else clause when blank, it appears as null. and when I enter 2 dashes, it displays as charachers and shows the date 1900:00:00:00.

    What I am trying to achieve is to subtract two dates (st3.Starttime - st.Starttime) then multiply by * 24*60 (format hh:mm:ss) once I determine the starttime for when a car occupied a space and the starttime for when a car vacate a space. I need to know the time that elapse when a car vacate before the next car occupied the space.

    The name of the occupied/vacate field is called "state". In my code above , I did a self join to get two different starttimes, then I use case statements to determine starttime for occupied and starttime for vacant. (st3.Starttime = 'occupied' starttime = 'vacant'). Then problem is in the else clause I get nulls and when I enter ' ', I get the 19:00:00 date format. I need the else clause to read as blank so that it does not affect the dates that I am trying to subtract. Thanks again

    case when st3.State = 'occupied' then st3.StartTime

    then convert(varchar(12),(st3.Starttime), 8)

    else ' ' end as TotalA,

    case

    when st.State = 'vacant' and then st.StartTime

    when convert(varchar(12),(st.Starttime), 8)

    else ' ' end as TotalB

    Any suggession would be greatly appreciated.

    Thanks,

  • I could hardly understand your question.

    Could you please provide table structure, sample data, your query as whole, the results you get and the results you want?


    I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
    Blog: http://somewheresomehow.ru[/url]
    Twitter: @SomewereSomehow

  • MS SQL implicitly casts a blank string into 1900-01-01 00:00:00 in datetime data type so you're a bit stuck there.

    Not sure why the NULLs are a problem though in your calculation?

  • Gazareth (8/6/2012)


    MS SQL implicitly casts a blank string into 1900-01-01 00:00:00 in datetime data type so you're a bit stuck there.

    Not sure why the NULLs are a problem though in your calculation?

    Expanding on this a little, you are asking SQL Server to return either a date, or a string, in the same column. It can't do that. So it does the conversion Gazereth mentioned.

    If you were to convert the date you select to a string, you might get what you want. I say 'might', because I don't 100% understand all of your post.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 4 posts - 1 through 3 (of 3 total)

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