SQL Server Performance

Convert Function

Discussion in 'Getting Started' started by Asif Ali, Jun 7, 2007.

  1. Asif Ali New Member

    I am writing a query on a table having yearly data. When I applied convert function on closedate field type datetime then it will shows the records of previous years
    -- closedate filed holding date and time both
    select * from table1 where convert(char,closedate,101) between '05/01/2007' and '05/03/2007'
    It shows the records of the year 2006 for the same days lies in between operator.

    If I removed the convert function then it shows records for onlr two days means the records from '05/03/2007' is not shown.
    select * from table1 where closedate between '05/01/2007' and '05/03/2007'
  2. Adriaan New Member

    You're asking SQL Server to compare a date string (not the date value) to a written date. Range criteria for strings work left-to-right, so the start of the string is the most significant part. In your format, you're finding all May 1st, 2nd and 3rd dates in any given year.

    Use the 120 switch for CONVERT, and the yyyy-mm-dd format for spelling out the dates.
  3. Asif Ali New Member

    I have found a that if use cast function over convert function on the same query then the correct result set will achieved

    select * from table1 where cast(convert(char,closedate,101) as datetime) between '05/01/2007' and '05/03/2007'
  4. Adriaan New Member

    Excellent work-around, as long as you understand how (and why) it works.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  5. Madhivanan Moderator

Share This Page