Select records between a date range

Question

Post reply

Select records between a date range

Nisha-475382

Hall of Fame

Points: 3535
More actions
January 3, 2008 at 9:43 pm

#185339

This is my query:
SELECT * FROM [MyDateTable]
WHERE CONVERT(VARCHAR,StartDate,101)
BETWEEN '02/01/2007' AND '01/04/2008'
It return absolutely nothing, although there are eleven eligible records within this date range. few in Feb 2007 and others in May 2007.
What could be wrong in this?

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

It's because you're converting the dates to text... try this, instead...

SELECT * FROM [MyDateTable]

WHERE StartDate >= '02/01/2007'

AND StartDate < '01/05/2008'

Notice that the "<" is in relation to the NEXT day after you want... this is to allow all the times on 01/04/2008 to be included without a conversion to a whole date. This method also allows for the use of an index "seek" whereas your method will not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Don't convert your dates to string - convert your strings to dates. As of right now you're comparing string values (where '02/01/2007' >'01/28/2008').

you want:

SELECT * FROM [MyDateTable]

WHERE StartDate

BETWEEN cast('02/01/2007' as datetime)

AND cast('01/04/2008' as datetime)

of course - you can also let T-SQL do the implicit conversion for you with:

SELECT * FROM [MyDateTable]

WHERE StartDate

BETWEEN '02/01/2007' --this gets converted to a date

AND '01/04/2008' --this gets converted to a date

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 3

Perfect, Matt! That's just what I wanted. Figured it out only after i hurriedly posted the query 🙂

Thanks, Jeff!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

No... it's not perfect if you want to include the whole day of 01/04/2008...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 5

What's perfect is: 'Don't convert your dates to string - convert your strings to dates'

That's all i needed 🙂 cuz the query I've written uses the comparison operators '>=' and '<='.. which is why I said: 'Thanks, Jeff'!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

You're missing the point... you will not get all of 01/04 using BETWEEN...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 7

Ok.. Here's my query:

SELECT * FROM [MyDateTable]

WHERE StartDate >= CONVERT(DATETIME, '02/01/2007')

AND StartDate <= CONVERT(DATETIME, '01/04/2008')

This works fine, Jeff! Which is why I was saying, I used Matt's ''don't convert your date field to string'' and I used your ' operators instead of the BETWEEN'.

Do you think I've gotten the point now? 🙂

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

No...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Unless none of your dates have times on them, the original query and the replacement query are bad...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Nisha (1/4/2008)
Ok.. Here's my query:
SELECT * FROM [MyDateTable]
WHERE StartDate >= CONVERT(DATETIME, '02/01/2007')
AND StartDate <= CONVERT(DATETIME, '01/04/2008')
This works fine, Jeff! Which is why I was saying, I used Matt's ''don't convert your date field to string'' and I used your ' operators instead of the BETWEEN'.
Do you think I've gotten the point now? 🙂

Yes, except that if there's a row with a start time of midnight on the 4th you will get it, but you won't get any other records for the 4th. (eg 2008/01/04 00:01, 2008/01/04 07:51, ...)

If you need all the rows that have startdates on the 4th, then use

StartDate < CONVERT(DATETIME, '01/05/2008')

If you don't need anything from the 4th, then use

StartDate < CONVERT(DATETIME, '01/04/2008')

Datetimes are date and time, and you need to take that into account with your queries.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 11

ok tell me exactly what I should do, Jeff.. I'm in trouble here then..!

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 12

Silly me!! Haha! Ok now i get what you guys are trying to say! I didn't pay much attention to that cuz I don't have any records for the 4th!

Silly silly!

Thanks a ton, guys!

This was great!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Pleasure. Are you all sorted now?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nisha-475382 Hall of Fame Points: 3535 More actions · Answer 14

Totally sorted out!!

You know most of the times, mistakes are out of pure carlessness or oversight rather than lack of knowledge.. I can't always say that for me cuz I'm only a fresher right out of college, at my first job! Haha.

But even at such a young age I'm subject to a great amount of pressure and I always seem to be too 'hurried' to read things right! Its terrible!

Which is why, being here is such a great boon!

Thanks again, guys!