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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » How do I remove nulls from case statements...

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

Rate Topic

Display Mode

Topic Options

Author

Message

leonie6214

Posted Sunday, August 05, 2012 1:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 4:18 AM
Points: 35, Visits: 212

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,

Post #1340243

SomewhereSomehow

Posted Sunday, August 05, 2012 7:45 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 10:52 PM
Points: 75, Visits: 410

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
Twitter: @SomewereSomehow

Post #1340270

Gazareth

Posted Monday, August 06, 2012 5:35 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:53 AM
Points: 1,474, Visits: 2,342

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?

Post #1340526

Phil Parkin

Posted Monday, August 06, 2012 6:39 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 4,247, Visits: 9,500

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.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1340553

« Prev Topic | Next Topic »

Permissions