How to convert two datesand get the max

Hi there,
How can i get the MAX dates between two dates?

this is what i have on my result set:
Column1       Status            Date1                                  Date2
B07-100043      ISSUED      2008-03-20 00:00:00.000      2009-04-14 00:00:00.000
B07-100072      APPLIED      2009-04-20 00:00:00.000      2009-04-20 00:00:00.000
B07-100372      APPLIED      2009-04-20 00:00:00.000      2009-04-20 00:00:00.000
B07-100559      APPLIED      2009-04-20 00:00:00.000      2009-04-20 00:00:00.000
B07-100588      ISSUED      2008-03-18 00:00:00.000      2009-04-20 00:00:00.000

and I need to get the latest date of the two in a third column. How can i do this?
Thanks,
COHFL
COHFLAsked:
Who is Participating?
 
Nathan RileyConnect With a Mentor FounderCommented:
Are they datetime columns or varchar?  Not sure if it matters, but try this:
SELECT NUMBER_KEY, 
STATUS, 
Expired_Date_Indicator,
lastInspection, 
CASE WHEN cast(Expired_Date_Indicator as datetime) > cast(lastInspectionas datetime) then cast(Expired_Date_Indicator as datetime) else cast(lastInspection as datetime) end as Third
FROM uw_ExpireCandidates

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you clarify the end result of your input data?
0
 
Nathan RileyFounderCommented:
You want the latest date in the 3rd column?


select distinct max(Date1)
from table
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
dan_nealConnect With a Mentor Commented:
Is this what you mean:
case date1 > date2 then date1 else date1 end
0
 
COHFLAuthor Commented:
ok this is what my where clause looks like but it does not work:

CASE WHEN ISNULL(DATE_L, ISNULL(ISSUED_DATE, isnull(CREATED_DATE, ENTERED_DATE)))
> I.LAST_INSP then
ISNULL(DATE_L, ISNULL(ISSUED_DATE, isnull(CREATED_DATE, ENTERED_DATE))) else
I.LAST_INSP end
 > DATEADD(day, - 180, GETDATE())

the result set is giving me this:
B07-100037      ISSUED      2008-11-05 00:00:00.000      2007-06-05 00:00:00.000      2008-11-05 00:00:00.000
B07-100149      ISSUED      2007-07-19 00:00:00.000      2009-01-09 00:00:00.000      2009-01-09 00:00:00.000
B07-100995      ISSUED      2008-01-04 00:00:00.000      2008-12-08 00:00:00.000      2008-12-08 00:00:00.000
B07-101263      ISSUED      2009-04-07 00:00:00.000      2009-04-20 00:00:00.000      2009-04-20 00:00:00.000
B07-101422      ISSUED      2008-11-10 00:00:00.000      2007-09-20 00:00:00.000      2008-11-10 00:00:00.000

which i know is not right becasue 180 days ago was 2008 and some 2009 is showing up
Can anyone help me
0
 
COHFLAuthor Commented:
ok since this looks like a mess i did everything all over agian. Here is my statement:
SELECT NUMBER_KEY,
STATUS,
Expired_Date_Indicator,
lastInspection,
CASE WHEN Expired_Date_Indicator > lastInspection then Expired_Date_Indicator else lastInspection end as Third
FROM uw_ExpireCandidates

this output something like this:
B07-100024      APPLIED      05/07/2007      05/07/2007      05/07/2007
B07-100037      ISSUED      11/05/2008      06/05/2007      11/05/2008
B07-100041      APPLIED      05/08/2007      05/08/2007      05/08/2007
B07-100043      ISSUED      03/20/2008      04/14/2009      04/14/2009
B07-100072      APPLIED      05/09/2007      05/09/2007      05/09/2007
B07-100079      ISSUED      11/27/2007      01/15/2009      11/27/2007
B07-100098      ISSUED      04/11/2008      07/10/2007      07/10/2007
B07-100135      APPLIED      05/11/2007      05/11/2007      05/11/2007
B07-100149      ISSUED      07/19/2007      01/09/2009      07/19/2007

Which is not true.
On line 6 the out shows that 11/27/2007 is  latest  than 01/15/2009 (crazy right)
"B07-100079      ISSUED      11/27/2007      01/15/2009      11/27/2007"
HOw can i fix my statement to do what i want?
0
 
COHFLAuthor Commented:
actually you wont believe but thats what i just tried:
SELECT NUMBER_KEY,
STATUS,
CAST(Expired_Date_Indicator as datetime) AS Expired_Date_Indicator,
CAST(lastInspection as datetime) AS lastInspection,
CASE WHEN CAST(Expired_Date_Indicator as datetime) > CAST(lastInspection as datetime)
then CAST(Expired_Date_Indicator as datetime) else CAST(lastInspection as datetime) end as Third
FROM uw_ExpireCandidates

ORDER BY
CASE WHEN CAST(Expired_Date_Indicator as datetime) > CAST(lastInspection as datetime)
then CAST(Expired_Date_Indicator as datetime) else CAST(lastInspection as datetime) end desc

Now how do i get the 180 days ago since the "THIRD"
0
 
Nathan RileyFounderCommented:
You mean you want an additional column that has the "THIRD" column -180 days?
0
 
COHFLAuthor Commented:
nevermind i got it:

SELECT NUMBER_KEY,
STATUS,
CAST(Expired_Date_Indicator as datetime) AS Expired_Date_Indicator,
CAST(lastInspection as datetime) AS lastInspection,
CASE WHEN CAST(Expired_Date_Indicator as datetime) > CAST(lastInspection as datetime)
then CAST(Expired_Date_Indicator as datetime) else CAST(lastInspection as datetime) end as Third
FROM uw_ExpireCandidates
WHERE
CASE WHEN CAST(Expired_Date_Indicator as datetime) > CAST(lastInspection as datetime)
then CAST(Expired_Date_Indicator as datetime) else CAST(lastInspection as datetime) end
<  DATEADD(day, - 180, GETDATE())

ORDER BY
CASE WHEN CAST(Expired_Date_Indicator as datetime) > CAST(lastInspection as datetime)
then CAST(Expired_Date_Indicator as datetime) else CAST(lastInspection as datetime) end desc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.