selecting a date range - 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 2008 Forums

Transact-SQL (2008)

selecting a date range

Author

Topic

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2009-05-14 : 11:36:28

i'm trying to use this search to bring back some data which i know exists

SELECT id, email, playDate
FROM tblPlayStats
WHERE (playDate BETWEEN 23 / 04 / 2009 AND 06 / 05 / 2009)

my dates are stored as dateTime in this example format

23/04/2009 10:38:06
date/month/year time

nothing is coming back from my select - any ideas anybody?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 11:42:09

[code]SELECT id, email, playDate
FROM tblPlayStats
WHERE (playDate BETWEEN '20090423' AND '20090506')[/code]

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-05-14 : 11:42:50

SELECT id, email, playDate
FROM tblPlayStats
WHERE (playDate BETWEEN '2009-04-23' AND '2009-05-06')

Or better would be:

SELECT id, email, playDate
FROM tblPlayStats
WHERE (playDate >= '2009-04-23' AND playDate <= '2009-05-06')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-15 : 01:55:15

More accurately

SELECT id, email, playDate
FROM tblPlayStats
WHERE (playDate >= '2009-04-23' AND playDate < '2009-05-07')

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-15 : 11:37:41

quote:
Originally posted by madhivanan

More accurately

SELECT id, email, playDate
FROM tblPlayStats
WHERE (playDate >= '2009-04-23' AND playDate < '2009-05-07')

Madhivanan

Failing to plan is Planning to fail

How is that more accurate? If the dates include time that will get almost an entire days worth of data that the original query would not.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-15 : 11:54:13

quote:
Originally posted by Lamprey

quote:
Originally posted by madhivanan

More accurately

SELECT id, email, playDate
FROM tblPlayStats
WHERE (playDate >= '2009-04-23' AND playDate < '2009-05-07')

Madhivanan

Failing to plan is Planning to fail
How is that more accurate? If the dates include time that will get almost an entire days worth of data that the original query would not.

The idea is that the end datetime will be the first point in time that you do not want to include.

This prevents having to write a query in this form:
playDate >= '2009-04-23' AND playDate <= '2009-05-06 23:59:59.997'
something that would have to be changed if you are using the new SQL 2008 datetime2:
playDate >= '2009-04-23' AND playDate <= '2009-05-06 23:59:59.9999999'

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-15 : 13:39:20

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by Lamprey

quote:
Originally posted by madhivanan

More accurately

SELECT id, email, playDate
FROM tblPlayStats
WHERE (playDate >= '2009-04-23' AND playDate < '2009-05-07')

Madhivanan

Failing to plan is Planning to fail
How is that more accurate? If the dates include time that will get almost an entire days worth of data that the original query would not.

The idea is that the end datetime will be the first point in time that you do not want to include.

This prevents having to write a query in this form:
playDate >= '2009-04-23' AND playDate <= '2009-05-06 23:59:59.997'
something that would have to be changed if you are using the new SQL 2008 datetime2:
playDate >= '2009-04-23' AND playDate <= '2009-05-06 23:59:59.9999999'

CODO ERGO SUM

I'm fully aware of how to select dates and I agree with you. I personally try to avoid less-than-or-equal-to when comparing against the end date. I was just curious why Mahdi thought it was more accurate when it was actually less accurate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 13:59:04

i think Madhi was telling about equivalence of using BETWEEN
&

WHERE (date >= startdate AND playDate < enddate.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-16 : 01:55:49

quote:
Originally posted by Lamprey

quote:
Originally posted by madhivanan

More accurately

SELECT id, email, playDate
FROM tblPlayStats
WHERE (playDate >= '2009-04-23' AND playDate < '2009-05-07')

Madhivanan

Failing to plan is Planning to fail
How is that more accurate? If the dates include time that will get almost an entire days worth of data that the original query would not.

OP specified that Time is also saved along with date. So I thought OP wanted to get all data on 06/05/2009 regardless of Time.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources