• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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
0
COHFL
Asked:
COHFL
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you clarify the end result of your input data?
0
 
Nathan RileyFounder/CTOCommented:
You want the latest date in the 3rd column?


select distinct max(Date1)
from table
0
 
dan_nealCommented:
Is this what you mean:
case date1 > date2 then date1 else date1 end
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
Nathan RileyFounder/CTOCommented:
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
 
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 RileyFounder/CTOCommented:
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now