Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Max of 2 dates Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 2:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:26 AM
Points: 268, Visits: 668
I search for Date functions for finding the max of the 2 dates. But such function does not exits.
Is there any easy way to do it.
Thanks in advance.
Post #1467035
Posted Tuesday, June 25, 2013 2:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:48 PM
Points: 1,913, Visits: 19,464
Shadab Shah (6/25/2013)
I search for Date functions for finding the max of the 2 dates. But such function does not exits.
Is there any easy way to do it.
Thanks in advance.


are the dates in same column or in different columns.....please post some sample data


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1467038
Posted Tuesday, June 25, 2013 2:49 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 25, 2014 2:41 AM
Points: 689, Visits: 2,786
Shadab Shah (6/25/2013)
I search for Date functions for finding the max of the 2 dates. But such function does not exits.
Is there any easy way to do it.
Thanks in advance.


this may help http://msdn.microsoft.com/en-us/library/ms187751.aspx


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1467040
Posted Tuesday, June 25, 2013 3:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:26 AM
Points: 268, Visits: 668
J Livingston SQL (6/25/2013)
Shadab Shah (6/25/2013)
I search for Date functions for finding the max of the 2 dates. But such function does not exits.
Is there any easy way to do it.
Thanks in advance.


are the dates in same column or in different columns.....please post some sample data


Hi ,
The colums are present in 2 separate tables with datatype Date. I want the date which would be max of both.
Table1 (col1 Date)
Table2 (col2 Date)

Table1('2013-06-25') and Table2('2013-06-24')

So the answer would be 2013-06-25





Post #1467046
Posted Tuesday, June 25, 2013 3:13 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 25, 2014 2:41 AM
Points: 689, Visits: 2,786
Something like this?

WITH CTE
AS
(
SELECT Date
FROM
Table1

UNION ALL

SELECT date
FROM
Table2
)

SELECT MAX(Date)
FROM
CTE


Andy


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1467047
Posted Tuesday, June 25, 2013 12:48 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 590, Visits: 2,753
Shadab Shah (6/25/2013)
J Livingston SQL (6/25/2013)
Shadab Shah (6/25/2013)
I search for Date functions for finding the max of the 2 dates. But such function does not exits.
Is there any easy way to do it.
Thanks in advance.


are the dates in same column or in different columns.....please post some sample data


Hi ,
The colums are present in 2 separate tables with datatype Date. I want the date which would be max of both.
Table1 (col1 Date)
Table2 (col2 Date)

Table1('2013-06-25') and Table2('2013-06-24')

So the answer would be 2013-06-25


I created sample data with a few records and primary key. Note the code and the how we get the max date
-- (1) Create Sample Data 
----------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#d1') IS NOT NULL
DROP TABLE #d1;
IF OBJECT_ID('tempdb..#d2') IS NOT NULL
DROP TABLE #d2;

CREATE TABLE #d1 (id int primary key, col1 date not null);
CREATE TABLE #d2 (id int primary key, col2 date not null);

INSERT INTO #d1
SELECT 1,'2013-06-25' UNION ALL SELECT 2,'2013-05-21' UNION ALL SELECT 3,'2013-05-05';
INSERT INTO #d2
SELECT 1,'2013-06-24' UNION ALL SELECT 2,'2013-05-22' UNION ALL SELECT 3,'2013-05-01'


-- (2) Get the maxdate
----------------------------------------------------------------------------------
SELECT #d1.id,
CASE WHEN col1>col2 THEN col1 ELSE col2 END AS maxdate
FROM #d1
JOIN #d2 ON #d1.id=#d2.id



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1467322
Posted Tuesday, June 25, 2013 1:09 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:09 PM
Points: 3,108, Visits: 11,502

select top 1
a.[Date]
from
(
select top 1 b.[Date] from table1 b order by b.[Date] desc
union
select top 1 c.[Date] from table2 c order by c.[Date] desc
) a
order by
a.[Date] desc


Post #1467334
Posted Tuesday, June 25, 2013 1:47 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 590, Visits: 2,753
Michael Valentine Jones (6/25/2013)

select top 1
a.[Date]
from
(
select top 1 b.[Date] from table1 b order by b.[Date] desc
union
select top 1 c.[Date] from table2 c order by c.[Date] desc
) a
order by
a.[Date] desc




I think he has two tables and is trying to get the max date for each row. If he was trying to get the max date for both columns (a single value as your query would return) then this would be cleaner and faster:

SELECT MAX([date]) [Date] FROM 
( SELECT [date] FROM table1
UNION
SELECT [date] FROM table2) AS a



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1467363
Posted Tuesday, June 25, 2013 2:12 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:48 PM
Points: 1,913, Visits: 19,464
Alan.B (6/25/2013)
Michael Valentine Jones (6/25/2013)

select top 1
a.[Date]
from
(
select top 1 b.[Date] from table1 b order by b.[Date] desc
union
select top 1 c.[Date] from table2 c order by c.[Date] desc
) a
order by
a.[Date] desc




I think he has two tables and is trying to get the max date for each row. If he was trying to get the max date for both columns (a single value as your query would return) then this would be cleaner and faster:

SELECT MAX([date]) [Date] FROM 
( SELECT [date] FROM table1
UNION
SELECT [date] FROM table2) AS a



as per OP request

Hi ,
The colums are present in 2 separate tables with datatype Date. I want the date which would be max of both.
Table1 (col1 Date)
Table2 (col2 Date)

Table1('2013-06-25') and Table2('2013-06-24')

So the answer would be 2013-06-25


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1467375
Posted Tuesday, June 25, 2013 2:43 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 590, Visits: 2,753
J Livingston SQL (6/25/2013)
Alan.B (6/25/2013)
Michael Valentine Jones (6/25/2013)

select top 1
a.[Date]
from
(
select top 1 b.[Date] from table1 b order by b.[Date] desc
union
select top 1 c.[Date] from table2 c order by c.[Date] desc
) a
order by
a.[Date] desc




I think he has two tables and is trying to get the max date for each row. If he was trying to get the max date for both columns (a single value as your query would return) then this would be cleaner and faster:

SELECT MAX([date]) [Date] FROM 
( SELECT [date] FROM table1
UNION
SELECT [date] FROM table2) AS a



as per OP request

Hi ,
The colums are present in 2 separate tables with datatype Date. I want the date which would be max of both.
Table1 (col1 Date)
Table2 (col2 Date)

Table1('2013-06-25') and Table2('2013-06-24')

So the answer would be 2013-06-25


Which is why I included two solutions: My original solution which I believe is correct (Shadab, please feel free to chime in ;) ) and one which was cleaner and faster than the one Michael posted.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1467387
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse