Home Forums SQL Server 2008 T-SQL (SS2K8) Max of 2 dates RE: Max of 2 dates

  • June 25, 2013 at 12:48 pm

    #1627262

    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