date time query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

date time query

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-07-13 : 17:40:21

Hi friends

whats wrong with following query it returns incorrect results

declare @x varchar(15)
set @x= '''03/11/2003'''

select * from task where convert(varchar(10),task.entrydt,103) >= @x

it works only if i do
select * from task where convert(varchar(10),task.entrydt,103) = @x

but i want to be able to do >,>=, <= on dates
Thanks for any ideas

Cheers

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-07-13 : 19:37:04

If you want to compare dates with operators you must compare them as date datatypes.

As your query is currently, you arer comparing two strings, right?

Youll see what I mean, look at how these dates are ordered when converted to varchar datatype:

declare @date table (testDate datetime)
insert into @date
	select getdate() union
	select getdate()-5 union
	select getdate()-10 union
	select '03/11/2003'


select convert(varchar(10),testDate,103)
from @date 
order by 1

select testDate
from @date 
order by 1

@x needs to either be a datetime, or converted to one to be compared against another datetime (task.entrydt).

Nathan Skerl

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-07-13 : 19:43:54

What are the resulting that you are getting versus the results you desire?

Note that you say you want to do comparisons on dates but you are converting the data to a string and comparing it to another string. Perhaps what you want to do is convert the @x string into a datetime and leave the datetime date as a datetime data item.

Ex.

select *
from task
where task.EntryDate >= Convert(datetime, @x)

As a minor observation, I avoid using double quotes as a string delimiter. A change in your server configuration could yield unexpected systax errors since that change could imply that quoted strings are actually identifiers. Use single quotes instead.

HTH

=================================================================
The most tyrannical of governments are those which make crimes of opinions, for everyone has an inalienable right to his thoughts. -Baruch Spinoza, philosopher (1632-1677)

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-07-13 : 21:06:48

Thanks Nathan ,Bustaz for ur replies

i changed my variable to a datetime field and tried following.
i have 2 records with following dates

2004-06-29 16:32:08.000
2004-06-29 12:37:18.000

when i run following query

declare @x datetime
set @x= '29/06/2004'

select taskid,taskname,entrydt from task where task.entrydt >= @x

i get following error

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-14 : 01:10:26

You have to use the format dd-mmm-yyyy or yyyymmdd

declare @x datetime
set @x= '29-Jun-2004'
--or
set @x= '20040629'
select taskid,taskname,entrydt from task where task.entrydt >= @x

Madhivanan

Failing to plan is Planning to fail

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-14 : 01:52:41


SET DATEFORMAT dmy
declare @x datetime
set @x= '29/06/2004'

Andy

Beauty is in the eyes of the beerholder

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-07-14 : 03:20:45

I always try to use ISO format CCYYMMDD for representing dates as there is less chance for confusion - is 1/2/03 the First of Feb or the second of Jan?

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources