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,
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.