Querying date time - 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)

Querying date time

Author

Topic

mn757
Starting Member

15 Posts

Posted - 2007-08-02 : 06:34:01

Hi,

I have a dateTime fields in the format of dd/mm/yyyy hh:mm:ss.

I am attempting to query this as follows;

select * from tablename where dateAndTime like 'dd/mm/yyyy %'

I am not interested in the hh:mm:ss data. I cant seem to do this, although I have done this using mysql without any issues. Apologies if this is a novice question, but any help appreciated.

My long term aim is to pass a date parameter to a stored procedure via an asp page.

many thanks,

Martin N.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-02 : 06:50:20

select * from tablename where dateAndTime = CONVERT(varchar(10),@Date,103)

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 07:00:11

select *
from tablename
where dateAndTime >= DATEADD(DAY, DATEDIFF(DAY, 0, @theDate), 0)
and dateAndTime < DATEADD(DAY, DATEDIFF(DAY, 0, @theDate), 1)

E 12°55'05.25"
N 56°04'39.16"

mn757
Starting Member

15 Posts

Posted - 2007-08-02 : 07:27:24

Hi,

Thanks both for your replies, however on each example I am getting out of date range error.

thanks,

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-02 : 07:28:31

Peso,
Had I already had a cup of coffee I would have done this
select *
from tablename
where CONVERT(varchar(10),dateAndTime,103) = @theDate

which still would be bad. I've seen you use the DATEADD(..DATEDIFF(... before. It's a lot more typing, is it more accurate, efficient? Not in comparison to my wrong answer, just in general.

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 07:33:32

Yes, but I can use any present index on the column which makes the searches much faster (index seek).
With your code, you force a table scan.

E 12°55'05.25"
N 56°04'39.16"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-02 : 07:44:05

Cool, Thanks! I'm self taught at SQL and am realizing I have a lot of bad habits -- solutions that are really just new problems -- and I am trying to unlearn them.

Jim
"Every day I learn something that somebody else already knew!"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-02 : 08:19:40

<<
I have a dateTime fields in the format of dd/mm/yyyy hh:mm:ss.
>>

What is the datatype of that column?

Madhivanan

Failing to plan is Planning to fail

mn757
Starting Member

15 Posts

Posted - 2007-08-02 : 09:10:28

Thnaks everyone, all sorted now.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-02 : 09:33:51

quote:
Originally posted by mn757

Thnaks everyone, all sorted now.

How?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources