January 9, 2004 at 7:26 am
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
January 9, 2004 at 7:36 am
WHERE LastDate = CONVERT(char(10),GETDATE(),111)
Why use varchar(10) for dates instead of one of the temporal data types?
--Jonathan
January 9, 2004 at 12:02 pm
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
January 11, 2004 at 4:41 pm
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
January 12, 2004 at 7:20 am
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
January 12, 2004 at 11:54 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy