SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Max of 2 dates


Max of 2 dates

Author
Message
Shadab Shah
Shadab Shah
SSC Eights!
SSC Eights! (879 reputation)SSC Eights! (879 reputation)SSC Eights! (879 reputation)SSC Eights! (879 reputation)SSC Eights! (879 reputation)SSC Eights! (879 reputation)SSC Eights! (879 reputation)SSC Eights! (879 reputation)

Group: General Forum Members
Points: 879 Visits: 798
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.
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11750 Visits: 37477
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

Andy Hyslop
Andy Hyslop
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2085 Visits: 3045
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
Shadab Shah
Shadab Shah
SSC Eights!
SSC Eights! (879 reputation)SSC Eights! (879 reputation)SSC Eights! (879 reputation)SSC Eights! (879 reputation)SSC Eights! (879 reputation)SSC Eights! (879 reputation)SSC Eights! (879 reputation)SSC Eights! (879 reputation)

Group: General Forum Members
Points: 879 Visits: 798
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
Andy Hyslop
Andy Hyslop
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2085 Visits: 3045
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
Alan.B
Alan.B
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12914 Visits: 8000
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13917 Visits: 11848


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



Alan.B
Alan.B
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12914 Visits: 8000
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11750 Visits: 37477
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

Alan.B
Alan.B
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12914 Visits: 8000
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 Wink ) and one which was cleaner and faster than the one Michael posted.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search