MS SQL compare dates ?



  • 5 Answer(s)

    By using this code one can easily compare the dates in Ms sql.

    SELECT CASE WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) ...
    

    The Test Case :

    WITH dates(date1, date2, date3, date4)
         AS (SELECT CAST('20101231 15:13:48.593' AS DATETIME),
                   CAST('20101231 00:00:00.000' AS DATETIME),
                   CAST('20101231 15:13:48.593' AS DATETIME),
                   CAST('20101231 00:00:00.000' AS DATETIME))
    SELECT CASE
             WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) THEN 'Y'
             ELSE 'N'
           END AS COMPARISON_WITH_CAST,
           CASE
             WHEN date3 <= date4 THEN 'Y'
             ELSE 'N'
           END AS COMPARISON_WITHOUT_CAST
    FROM dates
    

    Output :

    COMPARISON_WITH_CAST | COMPARISON_WITHOUT_CAST
    Y                      N
    
    Answered on December 24, 2018.
    Add Comment

    The DATEDIFF function can be able this with a datepart of day.

    SELECT ...
    FROM ...
    WHERE DATEDIFF(day, date1, date2) >= 0
    

        First we have to test that date1 <= date2 then you need to test that DATEDIFF(day, date1, date2) >= 0, or the different you can test DATEDIFF(day, date2, date1) <= 0.

    Answered on December 24, 2018.
    Add Comment

    Simply try this solutions for these various options except of “dd”,

    datediff(dd,'2010-12-31 15:13:48.593','2010-12-31 00:00:00.000')=0
     
    datediff(dd,'2010-12-31 15:13:48.593','2010-12-31 00:00:00.000')<=1
     
    datediff(dd,'2010-12-31 15:13:48.593','2010-12-31 00:00:00.000')>=1
    
    Answered on December 24, 2018.
    Add Comment

    Use the DATEDIFF function with a datepart of day.

    SELECT ...
    FROM ...
    WHERE DATEDIFF(day, date1, date2) >= 0

    Note that if you want to test that date1 <= date2 then you need to test that DATEDIFF(day, date1, date2) >= 0, or alternatively you could test DATEDIFF(day, date2, date1) <= 0.

    Answered on January 13, 2019.
    Add Comment
    SELECT CASE WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) ...

    Test Case

    WITH dates(date1, date2, date3, date4)
         AS (SELECT CAST('20101231 15:13:48.593' AS DATETIME),
                    CAST('20101231 00:00:00.000' AS DATETIME),
                    CAST('20101231 15:13:48.593' AS DATETIME),
                    CAST('20101231 00:00:00.000' AS DATETIME))
    SELECT CASE
             WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) THEN 'Y'
             ELSE 'N'
           END AS COMPARISON_WITH_CAST,
           CASE
             WHEN date3 <= date4 THEN 'Y'
             ELSE 'N'
           END AS COMPARISON_WITHOUT_CAST
    FROM   dates 

    Returns

    COMPARISON_WITH_CAST   |  COMPARISON_WITHOUT_CAST
    Y                         N
    Answered on January 13, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.