Selecting Greater of Two Dates

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

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting Greater of Two Dates

Author

Topic

Stanley Tan
Starting Member

25 Posts

Posted - 2004-09-02 : 18:02:19

Hi,

Given a table with two date fields (assume DateCreated and DateRevised), how can I select the greater of the two?

DateRevised = can be null

Or in other words, how can I select DateRevised if DateCreated is null?

Thanks. Any help is greatly appreciated.

Stan

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-02 : 18:05:10

[code]
SELECT CASE WHEN DateRevised IS NULL THEN DateCreated
WHEN DateCreated > DateRevised THEN DateCreated
ELSE DateRevised
[/code]

Stanley Tan
Starting Member

25 Posts

Posted - 2004-09-02 : 18:14:16

Thanks Dustin for the reply.

It doesn't seem to be working for me. What I want to do is create a SELECT statement that returns a single column (e.g. "Date") that will have the greater of the two columns (i.e. your CASE statement).

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-02 : 20:37:12

create function f_max(@a sql_variant, @b sql_variant) returns sql_variant AS
BEGIN
RETURN CASE when @a > @b THEN @a else @b END
END

SELECT dbo.f_max(DateRevised,DateCreated) Date
FROM MyTable

It can be extended for multiple columns with

SELECT dbo.f_max(dbo.f_max(DateRevised,DateCreated),DateAccessed) Date
FROM MyTable

and also used for other datatypes than dates.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-03 : 01:15:50

is there an overhead for using variant data types?

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-03 : 01:18:38

No it just makes the function generic in that you could use it to compare varchar, int, or other datatypes. You can change them to datetime if you wish.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources