CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ras
Ras

Reputation: 628

Query between datetime columns

I have a table with two DateTime columns, _dateEntry and _dateUpdate.

I've to create a query and choose the right datetime column based on these conditions: take the most recent between _dateEntry and _dateUpdate. _dateUpdate might be null, so I wrote this condition

SELECT id, description, ISNULL(_dateUpdate, _dateEntry) as date FROM mytable

but is not enought as I've to get also the most recent. How can I modify my query?

Upvotes: 2

Views: 88

Answers (4)

Joe Taras
Joe Taras

Reputation: 15399

I suppose _dateEntry is never NULL. If can be NULL tell me because I change my query

Try this:

SELECT id, description, 
CASE
   WHEN _dateUpdate IS NULL THEN _dateEntry
   WHEN _dateUpdate > _dateEntry THEN dateUpdate
   ELSE _dateEntry
END as date 
FROM mytable 
ORDER BY 
CASE
   WHEN _dateUpdate IS NULL THEN _dateEntry
   WHEN _dateUpdate > _dateEntry THEN dateUpdate
   ELSE _dateEntry
END desc

So if _dateUpdate IS NULL you'll get the value of _dateEntry, so the order is on two fields

Upvotes: 3

Nuru Salihu
Nuru Salihu

Reputation: 4948

SELECT id, description, ISNULL(_dateUpdate, _dateEntry) as date 
FROM mytable ORDER BY ISNULL(_dateUpdate, _dateEntry) desc

Upvotes: 3

Andrei Hirsu
Andrei Hirsu

Reputation: 696

You can use a case to return what you want:

SELECT
    id
    , description
    , CASE WHEN _dateEntry > ISNULL(_dateUpdate,0) 
           THEN _dateEntry ELSE _dateUpdate END AS date
FROM
    mytable

Upvotes: 4

Matt
Matt

Reputation: 15061

SELECT id, description, MAX(ISNULL(_dateUpdate, _dateEntry)) AS date 
FROM mytable
GROUP BY id, description

Upvotes: 3

Related Questions