Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I remove nulls from case statements with date fields? Expand / Collapse
Author
Message
Posted Sunday, August 05, 2012 1:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 1:54 PM
Points: 38, Visits: 220
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
Posted Sunday, August 05, 2012 7:45 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:03 AM
Points: 75, Visits: 437
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
Posted Monday, August 06, 2012 5:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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
Posted Monday, August 06, 2012 6:39 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:41 AM
Points: 4,828, Visits: 11,180
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1340553
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse