SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 need to compare two dates using only the lesser		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kofseattle
Starting Member

5 Posts
Posted - 05/25/2010 :  09:34:09  Show Profile  Reply with Quote
Hello,

I need to look at two date columns, choose the earliest of the two dates and then use that "AS" "Early Date" within my SQL qry. Does this make sense, can anyone help a newb?

webfred
Flowing Fount of Yak Knowledge

Germany
8513 Posts
Posted - 05/25/2010 :  09:38:11  Show Profile  Visit webfred's Homepage  Reply with Quote
select
case when datecol1 < datecol2 then datecol1 else datecol2 end as EarlyDate,
... other columns ...
fom table

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

bbakermai
Starting Member

USA
3 Posts
Posted - 03/14/2013 :  10:45:53  Show Profile  Reply with Quote
What if datecol1 is not null and datecol2 is null? datecol2 (NULL) will be returned since it is less than datecol1
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8513 Posts
Posted - 03/14/2013 :  11:14:33  Show Profile  Visit webfred's Homepage  Reply with Quote
quote:
Originally posted by bbakermai

What if datecol1 is not null and datecol2 is null? datecol2 (NULL) will be returned since it is less than datecol1

No, NULL isn't less than datecol1.
NULL is unknown hence datecol1 will be returned.

Too old to Rock'n'Roll too young to die.
Go to Top of Page

bbakermai
Starting Member

USA
3 Posts
Posted - 03/14/2013 :  14:25:37  Show Profile  Reply with Quote
I ran that equivalent code against a row where (essentially) datecol1 had a value and datecol2 did not, and got null returned. Try it.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5467 Posts
Posted - 03/14/2013 :  15:10:25  Show Profile  Reply with Quote
that's true.
depends what you want returned when datecol2 is null. assuming datecol1 then a coalesce thrown in there should work:

select
       case when datecol1 < datecol2 then datecol1 else coalesce(datecol2,datecol1) end as EarlyDate
from (
       select getdate() as datecol1, getdate()-1 as datecol2 union all
       select getdate()-1, getdate() union all
       select null, getdate() union all
       select getdate(), null
       ) d
output:

EarlyDate
-----------------------
2013-03-13 15:09:43.247
2013-03-13 15:09:43.247
2013-03-14 15:09:43.247
2013-03-14 15:09:43.247


Be One with the Optimizer
TG
Go to Top of Page

bbakermai
Starting Member

USA
3 Posts
Posted - 03/14/2013 :  16:11:12  Show Profile  Reply with Quote
Thank you! That did what I needed - get the earliest filled-in date of the two possibilities. I get stuck in C# thinking so much!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000