Query comparing dates in SQL

Query comparing dates in SQL

Asked on December 27, 2018 in SQL Server.
Add Comment


  • 5 Answer(s)

    Follow the procedure.

    Always use date like ‘20130412’. It is  recognized as the culture unchanging format .So use that instead of ‘2013-04-12’ which is the meaning of depends on the local culture,

    For example, compare with December 4th, we should write ‘20131204’. Similarly compare April 12th, we should write ‘20130412’.

    From SQL Server’s documentation a article explains how to write statements that are culture invariant called Write International Transact-SQL Statements:

    These applications can be used for other APIs, or Transact-SQL scripts, stored procedures, and triggers, should use the non- separated numeric strings. For example, yyyymmdd can be written as 19980924.

    The best way of parameterize the query and sendthe date value as a date parameter, if one using ADO . In this way one can neglet the format issue fully and gain the performance benefits of parameterized queries.

    UPDATE

    All the elements must be specified, to use the the ISO 8601 format in a verbatim . To quote from the ISO 8601 section of datetime’s documentation.

    In the deal with ISO 8601 format, we have to specify each element in the format. This also includes the T, the colons (:), and the period (.)  and so on that are shown in the format.

    The time component is specified in the 24-hour format.

    Answered on December 27, 2018.
    Add Comment

    One can simply use this code,

    select id,numbers_from,created_date,amount_numbers,SMS_text
    from Test_Table
    where
    created_date <= '2013-12-04'
    
    Answered on December 27, 2018.
    Add Comment

    Try to use this code,

    select id,numbers_from,created_date,amount_numbers,SMS_text
    from Test_Table
    where
    convert(datetime, convert(varchar(10), created_date, 102)) <= convert(datetime,'2013-04-12')
    
    Answered on December 27, 2018.
    Add Comment

    I have a table with dates that all happened in the month November. I wrote this query

    select id,numbers_from,created_date,amount_numbers,SMS_text 
    from Test_Table
    where 
    created_date <= '2013-04-12'

    This query should return everything that happened in month 11 (November) because it happened before the date ‘2013-04-12’ (in December)

    But it’s only returning available dates that happened in days lesser than 04 (2013-04-12)

    Could it be that it’s only comparing the day part? and not the whole date?

    How to fix this?

    Created_date is of type date

    Date format is by default yyyy-dd-MM

    Answered on January 13, 2019.
    Add Comment

    if You are comparing only with the date vale, then converting it to date (not datetime) will work

    select id,numbers_from,created_date,amount_numbers,SMS_text 
     from Test_Table
     where 
     created_date <= convert(date,'2013-04-12',102)

    This conversion is also applicable during using GetDate() function

    Answered on January 13, 2019.
    Add Comment


  • Your Answer

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