Smalldatetime in where clause - 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

General SQL Server Forums

New to SQL Server Programming

Smalldatetime in where clause

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2007-09-09 : 22:32:36

What would the proper way to write the following query be? Thanks in advance.

Select * from mydb where smalldatetimefield > today's date.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-09 : 22:52:08

quote:
Originally posted by RichardSteele

What would the proper way to write the following query be? Thanks in advance.

Select * from mydb where smalldatetimefield > today's date.

Not sure exactly what you are asking, it can't be greater than todays date ever but it can be greater than or equal to, you can also convert the current date's time to 00:00:00 so that it counts from start of day.

See following:

SELECT *
FROM MyDB
WHERE SMALLDATETIMEFIELD >= CAST(CONVERT(varchar(10),GETDATE(),1)AS SMALLDATETIME)

Future guru in the making.

RichardSteele
Posting Yak Master

160 Posts

Posted - 2007-09-09 : 22:58:09

That's perfect. Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 05:53:46

Always try to keep original datatypes.

SELECT *
FROM MyDB
WHERE SMALLDATETIMEFIELD >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

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

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-10 : 09:39:46

quote:
Originally posted by Peso

Always try to keep original datatypes.

SELECT *
FROM MyDB
WHERE SMALLDATETIMEFIELD >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

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

I didn't think of that, but I like it.

Future guru in the making.

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-10 : 10:23:13

And just to be clear, but using DATEDIFF would cause a scan I believe

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 10:27:57

Even when not used over a column, just a function?

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 10:32:32

quote:
Originally posted by X002548

And just to be clear, but using DATEDIFF would cause a scan I believe

????

- Jeff
http://weblogs.sqlteam.com/JeffS

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-10 : 10:39:45

quote:
Originally posted by Peso

Even when not used over a column, just a function?

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

Huh?

Just in the Predicate

I guess

WHERE DATEDIFF(dd,ColDate,getDate()) > 0

I believe would cause a scan

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 10:50:30

No one suggested doing it that way.

- Jeff
http://weblogs.sqlteam.com/JeffS

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-10 : 11:45:33

So, which way is better?

Future guru in the making.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 12:24:27

quote:
Originally posted by Zoroaster

So, which way is better?

Future guru in the making.

This one:

SELECT *
FROM MyDB
WHERE SMALLDATETIMEFIELD >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

Remember: when you CONVERT, it is NO LONGER A DATETIME -- it just a VARCHAR. Using the above formula simply returns a true datetime value, rounded to midnight of the current date, which requires no conversions and will always be more efficient.

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-09-10 : 12:31:40

Although

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

probably looks like a complicated way to just chop off the time part there has been discussion about the fastest way to do this, and this method came out fastest (mainly because it avoids things like "convert-to-char-and-back-to-datetime-again"

), which is why it is often quoted on here.

For the performance test see here: http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=38940#120953

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 12:37:50

>>probably looks like a complicated way to just chop off the time part

Just remember that we never chop off the time part, we just round to midnight. Like rounding a MONEY value from $12.8396 to $12.0000 -- the decimal portion is always there.

- Jeff
http://weblogs.sqlteam.com/JeffS

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-10 : 12:42:50

quote:
Originally posted by jsmith8858

>>probably looks like a complicated way to just chop off the time part

Just remember that we never chop off the time part, we just round to midnight. Like rounding a MONEY value from $12.8396 to $12.0000 -- the decimal portion is always there.

- Jeff
http://weblogs.sqlteam.com/JeffS

I miss the date function in Oracle sometimes

Future guru in the making.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 12:53:00

Zoroaster -- here's some handy T-SQL functions that you might find useful, they are very basic and simple and personally I think every SQL database should have them if you are working with any date or time data:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-09-10 : 15:36:06

"I miss the date function in Oracle sometimes"

Well MS has gone completely overboard on the next version of SQL Server, including inconsistent naming and complex-ifying the whole 9-yards

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-10 : 15:41:08

quote:
Originally posted by jsmith8858

Zoroaster -- here's some handy T-SQL functions that you might find useful, they are very basic and simple and personally I think every SQL database should have them if you are working with any date or time data:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

That is handy, thanks Jeff!

Future guru in the making.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-10 : 15:42:48

quote:
Originally posted by Kristen

"I miss the date function in Oracle sometimes"

Well MS has gone completely overboard on the next version of SQL Server, including inconsistent naming and complex-ifying the whole 9-yards

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85099#323598

Kristen

Interesting, I just got a copy of Katmai beta, will start playing with it soon.

Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources