date time query

Question

Post reply

date time query

sqlpro

Hall of Fame

Points: 3859
More actions
July 13, 2005 at 5:11 pm

#69306

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

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Too many tics' and you;ll want to declare @x as smalldatetime

declare @x SmallDatetime

set @x= '03/11/2003'

Which will evaluate to 03/11/2003 00:00:000

select * from task where task.entrydt >= @x

Can you post a create table definition and some sample data if your still having problems.

sqlpro Hall of Fame Points: 3859 More actions · Answer 2

Hi Ray

thanks for quick response.The reason i have ticks ' is am creating a dynamic sql from front end apps.

anyway i tried ur suggestion and unfortunatly did not work.

some of my data

taskid, taskname, entrydt

1, tong's first project, 2005-05-17 11:44:46.000

10, new task, 2003-12-24 16:17:59.000

1000, Inactive providers are di, 2003-11-03 00:00:00.000

1001, After selecting Claim or , 2003-11-03 00:00:00.000

1002, While changing the provid, 2003-11-03 00:00:00.000

1003, Visual acuity’s that are , 2003-11-03 00:00:00.000

1004, A few sites are occassion, 2003-11-03 00:00:00.000

1005, Stoping Letters.TextData , 2003-11-03 00:00:00.000

1007, Need a Ctrl-l for is high, 2003-11-03 00:00:00.000

1008, the arc32 form that we h, 2003-11-03 00:00:00.000

1009, Unable to create new ACC , 2003-11-04 00:00:00.000

1011, G.S: Friday 03-Oct-2003 c, 2003-11-05 00:00:00.000

1012, Ctrl-F7 . Default stateme, 2003-11-05 00:00:00.000

1013, IMM RNZGP Report : after , 2003-11-04 00:00:00.000

1014, , 2003-11-06 00:00:00.000

1015, When the tick is removed , 2003-11-06 00:00:00.000

i ran following i got abv data

declare @x datetime

set @x= '06/11/2003'

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

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Well I guess I'm not sure whats not working,

Is EntryDT a datetime in the table?

Returns top 16 records where entryDT is newer or equal to @x.

Looks Ok to me.

sqlpro Hall of Fame Points: 3859 More actions · Answer 4

Yes Ray, entrydt is datetime field.

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."

ND-215452 SSCrazy Points: 2037 More actions · Answer 5

declare @x datetime

set @x= '29/06/2004' --datetime doesn't like this format. How about '29-Jun-2004' ??

OR

'29/Jun/2004' ?

sqlpro Hall of Fame Points: 3859 More actions · Answer 6

That works Ray.thanks for that

but guess what if i change query to

declare @x datetime

set @x= '29/june/2004'

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

it does not return any results.

probably it is looking at time portion .that's why i was trying to convert it to a string and removing time part.

ND-215452 SSCrazy Points: 2037 More actions · Answer 7

one way to get around it...

select taskid,taskname,entrydt from task

where task.entrydt >= @x and task.entrydt < dateadd(dd,1,@x)

sqlpro Hall of Fame Points: 3859 More actions · Answer 8

Thanks ND.

that works fine. actually am looking for some general code

as where condition can contain any of the following filters

>,<,>=,<=

any ideas please.

Thanks

ND-215452 SSCrazy Points: 2037 More actions · Answer 9

ND-215452

SSCrazy

Points: 2037

July 13, 2005 at 8:22 pm

#574223

Most of the times I go direct to books online.

philcart SSC-Forever Points: 47796 More actions · Answer 10

Ok, I'll skip past all the obvious arguments against using dynamic SQL

If you use the date format 'dd mmm yyyy' you should find your dates will convert correctly. Actually when you use the three character month, you can put the date parts in any order,

select '12 Dec 2004', CAST('12 Dec 2004' as datetime)
select 'Dec 12 2004', CAST('Dec 12 2004' as datetime)
select '2004 12 Dec', CAST('2004 12 Dec' as datetime)
select 'Dec 2004 12', CAST('Dec 2004 12' as datetime)

--------------------
Colt 45 - the original point and click interface

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 11

Use the ISO date format and it will not matter what DATEFORMAT your server or client uses:

declare @x varchar(8)

SET @x ='20030311'

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

select * from task where convert(varchar(8),task.entrydt,112) = @x

Andy

sqlpro Hall of Fame Points: 3859 More actions · Answer 12

Andy

I've 6 records with following date

2003-11-03 00:00:00.000

when i run ur second query it returns only 1 record!! i mean

declare @x varchar(8)

SET @x ='20030311'

select taskid,taskname,entrydt from task

where convert(varchar(8),task.entrydt,112) = @x

when i remove WHERE condition i get to see everything ,ofcourse

philcart SSC-Forever Points: 47796 More actions · Answer 13

And what does this return?

declare @x varchar(12)
SET @x ='11 Mar 2003'

select taskid,taskname,entrydt from task 
where entrydt = cast(@x as datetime)

--------------------
Colt 45 - the original point and click interface

Jorge Cordero Ten Centuries Points: 1144 More actions · Answer 14

Be sure about the datetime format, even if you read in a string, you should (pre) agree, the valid date(s) format(s).

If it differs from the host default datetime format, do the necessary convert(datetime,...) before you assign the value to @x (smalldatime)

But prefer @x as smalldatime because you may increase your chances of the proper index use. Otherwise, you may find yourself table scanning.