Optimal query for datetime values? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Optimal query for datetime values?

Author

Topic

nieurig
Starting Member

8 Posts

Posted - 2005-06-15 : 03:00:38

Hello folks,
I have some datetime values at my database stored with date only (no times) like this

CREATE TABLE myTable(
id INT IDENTITY,
mydate SMALLDATETIME NOT NULL)
GO
INSERT myTable VALUES('14/6/2005')
INSERT myTable VALUES('14/6/2005')
INSERT myTable VALUES('13/6/2005')

I need to select data from yesterday.
Well there are a lot of approaches to ask for these data

1.) Using the datefunctions
select * from myTable where
Year(myDate) = Year(Current_Timestamp-1) and
month(myDate) = month(Current_Timestamp-1) and
day(myDate) = day(Current_Timestamp-1)

2.) Work with Strings
select * from myTable where
convert(char(10),myDate,4) = convert(char(10), Current_Timestamp-1, 4)

3.) Work with datetimes
select * from myTable where
myDate = cast(convert(char(10), Current_Timestamp-1, 4) as datetime)

Because I have no database with a lot of data to develop the
optimal query i ask for your help.
What was the fastes way to get data in this case?
Do you have more efficient kind of querys?

Thank you for your help.
Niels

pipi
Starting Member

18 Posts

Posted - 2005-06-15 : 03:09:23

hi!!!try this

select *
from myTable
where datediff(day,getdate(),mydate)=-1

nieurig
Starting Member

8 Posts

Posted - 2005-06-15 : 04:20:49

Thanks,
that looks nice :-)

What do your think about performance comparing to my variations?

Niels

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-15 : 09:52:33

Just check the execution plans for all the variations. See which one is most efficient that way.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Kristen
Test

22859 Posts

Posted - 2005-06-15 : 15:15:45

select *
from myTable
where mydate >= @yesterday AND mydate < @today

wil probably work more quickly - any index on mydate should be used

You'll need to pre-calculate @yesterday and @today, or use DATEADD to calculate then from GetDate():

DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())) is today
DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())-1) is yesterday

Kristen

nieurig
Starting Member

8 Posts

Posted - 2005-06-16 : 01:27:12

Hello

@donAtWork
My problem was that i haven't lot of data at my database. The run of all version cost aprox the same resources.

@kisten
Well i use variables like you suggested into SP but i look for the best way to run ad hoc querys.

Thanks to all.
Niels

P.S. More hints wellcome

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-16 : 07:16:33

Then fill a test table that mirrors your real table with a lot of bogus data. Make it a million rows. THEN run the query.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources