By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,760 Members | 1,557 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,760 IT Pros & Developers. It's quick & easy.

selecting records based on date

P: n/a
I have a table that has a DateTime column which uses a DataTime
datatype. How do I retrieve a range of records based on the month and
year using ms sql?

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***
Mar 15 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Eugene Anthony wrote:
I have a table that has a DateTime column which uses a DataTime
datatype. How do I retrieve a range of records based on the month and
year using ms sql?
Google is your friend.

Results 1 - 10 of about 12,100 for
"microsoft sql server" "date functions". (0.27 seconds)
Mar 15 '07 #2

P: n/a
This works.

SELECT DATEPART(mm, Dates) AS month,DATEPART(yy, Dates) AS year FROM
testing

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***
Mar 16 '07 #3

P: n/a
On Mar 15, 12:44 pm, Eugene Anthony <solomon_13...@yahoo.comwrote:
I have a table that has a DateTime column which uses a DataTime
datatype. How do I retrieve a range of records based on the month and
year using ms sql?

Eugene Anthony

*** Sent via Developersdexhttp://www.developersdex.com***
I've always hated this one. Searching for a date like that is pretty
simple but looking for a range can often times return unwanted
results. Though I don't know if it is right or not, I've done the
following before in the past:

SELECT *
FROM table
WHERE CONVERT(char(2), DatePart(yy, table.datefield)) +
CONVERT(char(2), DatePart(mm, table.datefield)) >= CONVERT(char(2),
DatePart(yy, BeginDate)) + CONVERT(char(2), DatePart(mm, BeginDate))
AND CONVERT(char(2), DatePart(yy, table.datefield)) + CONVERT(char(2),
DatePart(mm, table.datefield)) <= CONVERT(char(2), DatePart(yy,
EndDate)) + CONVERT(char(2), DatePart(mm, EndDate))

This way, if begin date is 1/1/07 and end date is today it will
evaluate between 0701 and 0703. The problem with the above is that
since you can't index it it needs to do a full table scan and if it is
a large table this can sometimes take some time. If that is the case
and you run this often you might want to add a computed field that
carries the converted date over and index that sucker.

I hope that helps.

Utah

Mar 16 '07 #4

P: n/a
Something like : SELECT myCol1 FROM myTable WHERE DATEPART(mm, Dates) =
'<insert month>',DATEPART(yy, Dates) = '<insert year>'

--

Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com

"Eugene Anthony" <so***********@yahoo.comwrote in message
news:45*********************@news.qwest.net...
>I have a table that has a DateTime column which uses a DataTime
datatype. How do I retrieve a range of records based on the month and
year using ms sql?

Eugene Anthony

*** Sent via Developersdex http://www.developersdex.com ***

Mar 18 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.