Retrieving latest date from 2 fields in table

Question

Post reply

Retrieving latest date from 2 fields in table

Lorna-331036

SSC Eights!

Points: 867
More actions
November 18, 2008 at 4:04 am

#202950

I need to write some SQL to determine when a record in a table (UsedVehicles) was last updated. I have records of used cars and if my client adds a car, I store today's date (getdate()) in the DateCreated field for that record and if he amends a car, I store getdate() in the DateAmended field. In the Used vehicles section of the website, I need to determine the date the site was last updated, therefore, I need to retrieve the latest date between the 2 date fields, bearing in mind that for many records, the DateAmended will be null. I know I could do 2 separate queries but surely there must be an easier way. Any help would be much appreciated.
Thanks
Lorna

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Lorna

something like this should do the trick:

-- If the row has been amended, then the amended date will not be null; use the amend date.

-- If the row has not been amended then the amended date will be null; use the create date.

SELECT ... ISNULL(DateAmended, DateCreated) AS [latest date]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dva2007 SSCertifiable Points: 7707 More actions · Answer 2

Case when Date_Amended is null then Date_Created else Date_amended end as Latest_Date

Lorna-331036 SSC Eights! Points: 867 More actions · Answer 3

Thank you very much - will try this and post an example in case anyone else is interested 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Lorna Wheelhouse (11/18/2008)
Thank you very much - will try this and post an example in case anyone else is interested 🙂

You're welcome Lorna. Incidentally, both statements produce the same result...

ISNULL(DateAmended, DateCreated) AS [latest date]

Case when Date_Amended is null then Date_Created else Date_amended end as Latest_Date

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Just a warning... The proposed solutions are probably OK in this case, where DateCreated of a row will never change (at least one can assume it). It wouldn't work if the "original" date (DateCreated in posted example) is not creation date and gets updated in some situation.

Safer solution would be for example:

SELECT CASE WHEN ISNULL(DateAmended, DateCreated) > DateCreated THEN DateAmended ELSE DateCreated END as last_date

FROM ...

Lorna-331036 SSC Eights! Points: 867 More actions · Answer 6

Great thanks - works well. Only thing is it returns lots of values but I can sort DESC and then take the first record value -unless anyone knows an easier way?

Much appreciated.

Lorna

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Try this:

ORDER BY CASE WHEN ISNULL(DateAmended, DateCreated) > DateCreated THEN DateAmended ELSE DateCreated END DESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lorna-331036 SSC Eights! Points: 867 More actions · Answer 8

Thanks everyone - learning loads today from you guys 🙂