October 3, 2007 at 3:44 am
Hi All,
select 1 where convert(varchar(10),getdate(),103) <= convert(varchar(10),'01/01/2008',103)
How above condition is worng.
Regrads,
Kiruba sankar.S
October 3, 2007 at 4:00 am
Kiruba
Because you've converted your dates to varchar, it's comparing string values and not date values. In those circumstances, '03/10/07' will always be greater than '01/01/08'.
John
October 3, 2007 at 4:06 am
hi,
this will work
select 1 where getdate() <= '01/01/2008'
select 1 where getdate() <= convert(varchar(10),'01/01/2008',103)
since u are comparing strings
ravi
October 3, 2007 at 3:31 pm
If you want to compare dates in string format you must use the 111 date format, because this way you'l have first the year, then the month and finally the day. Try this:
declare @a datetime
set @a='2008-01-01'
select 1 where convert(varchar(10),getdate(),111) <= convert(varchar(10),@a,111)
Hope this helps,
Felix
October 3, 2007 at 3:37 pm
All 3 version of this query are read by the Query Optimizer in the same way: the string date on the right side is implicitly converted to datetime. Because of this and the fact that you want to use datetime always when comparing dates, I would suggest using the first version as it is easier to read.
select 1 where getdate() <= '01/01/2008'
select 1 where getdate() <= CAST('01/01/2008' as datetime)
select 1 where getdate() <= convert(varchar(10),'01/01/2008',103)
October 4, 2007 at 6:49 am
The second query is not 100% correct because it depends on the dateformat. For example:
set dateformat dmy;
select 1 where getdate() <= CAST('01/15/2008' as datetime)
will raise an error.
October 4, 2007 at 9:12 am
They will all produce an error if you change the date format. We must assume here that the varchar date value that they are placing on the right side of the equality is in the correct date format for their settings.
set dateformat dmy
select 1 where getdate() <= '01/15/2008'
select 1 where getdate() <= CAST('01/15/2008' as datetime)
select 1 where getdate() <= convert(varchar(10),'01/15/2008',103)
October 5, 2007 at 1:52 am
As others have said issue is the varchar comparison - I would use something like
select 1 where getdate() <= convert(datetime,convert(varchar(10),'01/01/2008',103),103)
James Horsley
Workflow Consulting Limited
October 5, 2007 at 11:05 am
select 1 where getdate() <= convert(datetime,convert(varchar(10),'01/01/2008',103),103)
Why do all the converting: char(10) to varchar(10) to datetime? And why lock yourself into a specific format? Let the system perform an implicit conversion and you can use any valid format. All the following work identically:
select 1 where getdate() <= '01/15/2008';
select 1 where getdate() <= '20080115';
select 1 where getdate() <= '2008-01-15';
select 1 where getdate() <= 'Jan 15, 2008';
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 5, 2007 at 11:48 am
Exactly!! There is no reason to CAST/CONVERT here as the Query Optimizer will do an implicit conversion. Hence my earlier suggestion to use the select 1 where getdate() <= '01/15/2008' statement. Thanks for putting us back on target Tomm.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply