August 5, 2012 at 1:49 am
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,
August 5, 2012 at 7:45 am
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?
August 6, 2012 at 5:35 am
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?
August 6, 2012 at 6:39 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply