month year where clause - 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

General SQL Server Forums

New to SQL Server Programming

month year where clause

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-17 : 06:00:49

Hi i have a where clause which i think i need to change

i currently have the code below but dont think it right
i think it needs to be (O_Date) between @month1 @year AND @month2 @year2, but not sure how this should be done?

WHERE MONTH(O_Date) Between @month1 AND @month2
and YEAR(OW.O_Date) Between @year AND @year2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 06:42:57

Something similar to

WHERE O_DATE >= dateadd(month, 12 * @Year1 - 22801 + @Month1, '19000101')
AND O_Date < dateadd(month, 12 * @Year2 - 22800 + @Month2, '19000101')

E 12°55'05.25"
N 56°04'39.16"

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-17 : 07:10:09

ok thanks i have tried that but when my asp page returns i get this error

Microsoft][SQL Native Client][SQL Server]Adding a value to a 'datetime' column caused overflow.

currently my parameter @month1 AND @month2 AND @year1 AND @year2 are all INT values would this be an issue?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 07:13:32

No. It depends on their values.

E 12°55'05.25"
N 56°04'39.16"

craigmacca
Posting Yak Master

142 Posts

Posted - 2007-12-17 : 08:35:57

ok the values are month = 07 and year = 2007

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 08:38:10

Try the query first with manual values to verify result.

WHERE O_DATE >= dateadd(month, 12 * 2007 - 22801 + 7, '19000101')
AND O_Date < dateadd(month, 12 * 2007 - 22800 + 9, '19000101')

to get all records between July 1, 2007 and September 30, 2007.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-17 : 08:40:20

try

where O_date>=dateadd(year,@year1-1900,dateadd(month,@month1-1,0))
and O_date<dateadd(year,@year2-1900,dateadd(month,@month2,0))

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-17 : 08:49:52

advice: declare two variables: @startdate and @endate (or something like that). Set them to values using a formula, such as the ones above, and then examine those variables to be sure that they are set to what you want BEFORE attempting to use them to filter data.

Then, when you are sure your formulas are working (be sure to test for different inputs), incorporate those variables into your where clause. ALWAYS do this when querying on a date range that is the result of some formula, that way a) you can be sure your formula is accurate and b) it will be optimally efficient since you can be sure the formula is only calculated once.

more here: http://weblogs.sqlteam.com/jeffs/archive/2007/09/14/sql-filter-by-month.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources