SQL - WHERE date range

Hello,

In a SQL Server database I have a column called datereceived.  In datereceived an example of a value is '20040513'.

How am I able to run a SQL SELECT statement and have the WHERE select a date?  So, for example, lets say I want to pull all data where datereceived is between Sept. 1 and Sept . 30.

Thanks in advance.
rudyflyerAsked:
Who is Participating?
 
acampomaCommented:
select * from TableName where datereceived  between '20040901' and '20040930'
0
 
Scott PletcherSenior DBACommented:
SELECT ...
FROM ...
WHERE dateReceived BETWEEN '20040901' AND '20040930'



Is dateReceived a [SMALL]DATETIME type or a plain numeric type, like INT?  If [SMALL]DATETIME, you need to add time to the last value:

WHERE dateReceived BETWEEN '20040901' AND '20040930 23:59'  -- smalldatetime
WHERE dateReceived BETWEEN '20040901' AND '20040930 23:59:59.997'  -- datetime

--NOTE: .997 is correct, *not* .999, which will round up to the next day.



0
 
rudyflyerAuthor Commented:
datereceived is numeric.

Would it be possible to have the the WHERE clause state:

...WHERE dateReceived BETWEEN '09/01/04' AND '09/30/04'.  

Thanks.
0
 
Scott PletcherSenior DBACommented:
Only if you convert dateReceived to a [small]datetime first.  The huge disadvantage to that is that now an index on dateReceived will not be used, even if one exists:


select *
from TableName
where CAST(datereceived AS SMALLDATETIME)  between '09/01/04' and '09/30/04'
0
 
Scott PletcherSenior DBACommented:
Sorry, just realized you accept the [same] answer from someone else.  I will get out of your way and allow him/her to respond.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.