Reply to Re: selecting records based on date

Your name:

Reply:


Posted by Utahduck on 03/16/07 14:36

On Mar 15, 12:44 pm, Eugene Anthony <solomon_13...@yahoo.com> 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?
>
> 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

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация