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