locked
Comparing dates RRS feed

  • Question

  • My_date is format is dd/mm/yyyy   like 07/02/2007

    my another date is like '09/15/2007'

    my query like below

    where Convert(varchar(20),Cast(my_date AS Datetime),103) > (here my date is coming like '09/15/2007')

    so how to write my where condition to get records which are after 2007

    Thanks


    Many Thanks coolboy


    Sunday, March 18, 2012 7:40 AM

Answers

All replies

  • I prefer to use dates in yyyy-mm-dd format whenever possible because it is never ambiguous.

    You should only convert dates to character types when display it to the user, however this would be best to do on the application tier instead of the database tier.

    You can compare dates like this

    WHERE My_date >= '2008-01-01'
    '2008-01-01' is equivalent to '2008-01-01 00:00:00.000'

    If you only want the rows from 2008 you can use this:

    WHERE My_date BETWEEN '2008-01-01' AND '2009-01-01'

    Sunday, March 18, 2012 8:05 AM
  • You can use ANSI format string date:

    WHERE My_date >= '20080101'
    
    -- Use local variable
    DECLARE @StartDate datetime = '20080101'
    ....
    WHERE My_date >= @StartDate

    Article of date range programming in T-SQL:

    http://www.sqlusa.com/bestpractices2008/between-dates/


    Kalman Toth SQL SERVER & BI TRAINING




    • Edited by Kalman Toth Friday, March 23, 2012 2:02 PM
    • Marked as answer by Naomi N Friday, March 23, 2012 2:20 PM
    Sunday, March 18, 2012 8:13 AM
  • I suggest to use SET DATEFORMAT  DMY; BEFORE SELECT STATEMENT.

    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    Sunday, March 18, 2012 8:25 AM