Max of 2 dates
-
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.
-
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 -
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 -
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
-
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 -
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
"I cant 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."-- Itzik Ben-Gan 2001
-
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
-
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
"I cant 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."-- Itzik Ben-Gan 2001
-
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 -
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.
"I cant 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."-- Itzik Ben-Gan 2001
-
Alan.B (6/25/2013)
Which is why I included two solutions: My original solution which I believe is correct and one which was cleaner and faster than the one Michael posted.Do you not test before making performance claims?
Michael Valentine Jones
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 117 ms.
Alan.B
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 269 ms.
-
Sean Pearce (6/26/2013)
Alan.B (6/25/2013)
Which is why I included two solutions: My original solution which I believe is correct and one which was cleaner and faster than the one Michael posted.Do you not test before making performance claims?
Michael Valentine Jones
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 117 ms.
Alan.B
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 269 ms.
First, good morning to you too. I am not trying to win a contest here and would like to think we are all on the same team. If I was mistaken it would not be the first time on this forum. That said, perhaps a friendlier tone is in order?
Regarding your question... I would not make performance claims without testing. Can you post the ddl you used to come up with those numbers? What are you comparing to what? I don't have a server handy at the moment but Id like to see how you came up with these results; they were different then mine. I will re-test this later this morning and post my results.
"I cant 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."-- Itzik Ben-Gan 2001
-
Alan.B (6/26/2013)
Sean Pearce (6/26/2013)
Alan.B (6/25/2013)
Which is why I included two solutions: My original solution which I believe is correct and one which was cleaner and faster than the one Michael posted.Do you not test before making performance claims?
Michael Valentine Jones
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 117 ms.
Alan.B
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 269 ms.
First, good morning to you too. I am not trying to win a contest here and would like to think we are all on the same team. If I was mistaken it would not be the first time on this forum. That said, perhaps a friendlier tone is in order?
Regarding your question... I would not make performance claims without testing. Can you post the ddl you used to come up with those numbers? What are you comparing to what? I don't have a server handy at the moment but Id like to see how you came up with these results; they were different then mine. I will re-test this later this morning and post my results.
Good afternoon.
I think the onus on supplying DDL for a performance test is on the person who claimed their code is faster.
Mine was simply creating two tables with a date column and loading 500,000 random dates.
-
Sean Pearce (6/26/2013)
Alan.B (6/26/2013)
Sean Pearce (6/26/2013)
Alan.B (6/25/2013)
Which is why I included two solutions: My original solution which I believe is correct and one which was cleaner and faster than the one Michael posted.Do you not test before making performance claims?
Michael Valentine Jones
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 117 ms.
Alan.B
SQL Server Execution Times:
CPU time = 812 ms, elapsed time = 269 ms.
First, good morning to you too. I am not trying to win a contest here and would like to think we are all on the same team. If I was mistaken it would not be the first time on this forum. That said, perhaps a friendlier tone is in order?
Regarding your question... I would not make performance claims without testing. Can you post the ddl you used to come up with those numbers? What are you comparing to what? I don't have a server handy at the moment but Id like to see how you came up with these results; they were different then mine. I will re-test this later this morning and post my results.
Good afternoon.
I think the onus on supplying DDL for a performance test is on the person who claimed their code is faster.
Mine was simply creating two tables with a date column and loading 500,000 random dates.
Fair enough, and please pardon me if I was rude (it was 5AM-ish [2 hours before Coffee] in Chicago when I replied to your post)...
You are correct Michael's Solution is a little quicker....
Code I used to test (The way I am testing here is not optimal... but I'm in a hurry):
/**************************************************
(1) Sample Data
**************************************************/
IF OBJECT_ID('tempdb..table1') IS NOT NULL
DROP TABLE table1;
IF OBJECT_ID('tempdb..table2') IS NOT NULL
DROP TABLE table2;
IF OBJECT_ID('tempdb..tally') IS NOT NULL
DROP TABLE tally;
CREATE TABLE table1 (id int primary key, [date] date not null);
CREATE TABLE table2 (id int primary key, [date] date not null);
CREATE TABLE tally (n int primary key);
;WITH
L0(c)AS(SELECT 1 UNION ALL SELECT 1 AS O), -- 2 rows
L1(c)AS(SELECT 1 FROM L0 [A] CROSS JOIN L0 ), -- 4 rows
L2(c)AS(SELECT 1 FROM L1 [A] CROSS JOIN L1 ) , -- 16 rows
L3(c)AS(SELECT 1 FROM L2 [A] CROSS JOIN L2 ), -- 256 rows
L4(c)AS(SELECT 1 FROM L3 [A] CROSS JOIN L3 ), -- 65,536 rows
L5(c)AS(SELECT 1 FROM L4 [A] CROSS JOIN L4 ), -- 4,294,967,296 rows
Tally_cte AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n FROM L5)
INSERT INTO tally
SELECT n FROM Tally_cte WHERE n<=500000;
INSERT INTO table1
SELECT n,
CAST(
DATEADD(day,DATEDIFF(day,0,GETDATE())- 1 - FLOOR(RAND(CAST(NEWID() AS binary(4)))*365.25*90),0) AS date)
FROM tally
INSERT INTO table2
SELECT n,
CAST(
DATEADD(day,DATEDIFF(day,0,GETDATE())- 1 - FLOOR(RAND(CAST(NEWID() AS binary(4)))*365.25*90),0) AS date)
FROM tally
--SELECT * FROM table1;
--SELECT * FROM table2;
/**************************************************
(2) Queries
**************************************************/
SET NOCOUNT ON
dbcc freeproccache
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
-- Michaels Solution
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;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
DBCC freeproccache
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
SELECT MAX([date]) [date] FROM
(SELECT [date] FROM table1
UNION
SELECT [date] FROM table2) AS a
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
Results:
Michael Valentine Jones
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 181 ms.
Alan.B
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 218 ms, elapsed time = 227 ms.
Honestly, I made the assumption that it was faster based on the estimated query plan. I know that is not always accurate but, in this case, it made sense.
I will stand by the statement that my code is cleaner and add that it is easier to read. Just showing different ways of doing this... 😉
Edit: Image did not post correctly.
"I cant 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."-- Itzik Ben-Gan 2001
-
Gentlemen,
We are all gentlemen here right? Is it too late to join the party, or skirmish as the case may be?
How's this one stack up in your test harness Alan?
DBCC freeproccache;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
WITH T1 (d1) AS (SELECT MAX([date]) FROM table1)
,T2 (d2) AS (SELECT MAX([date]) FROM table2)
SELECT [Date]=CASE WHEN d1 > d2 THEN d1 ELSE d2 END
FROM (SELECT d1=(SELECT d1 FROM T1), d2=(SELECT d2 FROM T2)) a;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
Looked pretty good when I tried it.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply