#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    1
    Rep Power
    0

    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    try

    Where LastDATE = convert(char(10),getdate(),111)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo