need to compare two dates using only the lesser

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need to compare two dates using only the lesser

Author

Topic

kofseattle
Starting Member

8 Posts

Posted - 2010-05-25 : 09:34:09

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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-25 : 09:38:11

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.

bbakermai
Starting Member

3 Posts

Posted - 2013-03-14 : 10:45:53

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-14 : 11:14:33

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.

bbakermai
Starting Member

3 Posts

Posted - 2013-03-14 : 14:25:37

I ran that equivalent code against a row where (essentially) datecol1 had a value and datecol2 did not, and got null returned. Try it.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-14 : 15:10:25

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

bbakermai
Starting Member

3 Posts

Posted - 2013-03-14 : 16:11:12

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources