SQL Date Problem

  • Friends,

    I have one query, I am using a table with a field name lastDate and Datatype is varchar(10) and i am storing the date in yyyy/mm/dd format in the table. Now I want a query which would compare the lastdate in the table with the currentdate and retreive the rows from the table matching currentdate

    The query that i had tried is as follows

    Select * FROM TableName

    Where LastDATE = cast(year(getdate()) as varchar(4))+'/'+cast(Month(getdate()) as varchar(2))+'/'+cast(Day(getdate()) as varchar(2))

    But this query doesnt give me any results matching for dates like 01/08/2003, 05/01/2003 etc. where there is a leading zero in month or day. because the cast function removes the leading zeros from month and day and gives the output like 1/8/2003, 5/1/2003 and so this doesn't matches with the dates in table as i have used varchar datatype for Lastdate field in table.

    Can anybody help me out for sorting out this query.

    Thnx in advance

    Parag Shah

  • WHERE LastDate = CONVERT(char(10),GETDATE(),111)

    Why use varchar(10) for dates instead of one of the temporal data types?



    --Jonathan

  • I agree with Jonathan that you should be using DATETIME or SMALLDATETIME as your datatype.  But if you really want to stick with char/varchar, then you should use CONVERT instead of CAST, and feed it an appropriate style parameter.  For example, this query retains leading zeroes (run today):

    select convert(varchar(10), getdate(), 101) as Today

    Today          

    ----------

    01/09/2004

    (1 row(s) affected)

    Hope this helps!

    Chris

  • To stop table scans and use your indexes don't do this:

     

    Where LastDATE = cast(year(getdate()) as varchar(4))+'/'+cast(Month(getdate()) as varchar(2))+'/'+cast(Day(getdate()) as varchar(2))

     

    A bunch of better ways to do it, one way would be:

    where datediff(dd,LastDATE,getdate()) < 1

    HTH

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • That's exactly backwards advice.  Putting the column values in a function makes the predicate non-sargable.  Even just converting the values (as your solution also does, implicitly) will prevent the use of any index on the column.



    --Jonathan

  • As you are not converting the actual column to a string, you might be right Jonathan, have to run some tests to confirm, also didn't realize it wasn't stored as a non-date column which would strike down mine anyway.

    Thanks for pointing that out

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply