March 22, 2004 at 7:24 am
Hi all
Enclosed is a part of a query i.e. the where clause bit:
where datepart(month,oh.date_despatched) = datepart(month,dateadd(month,-1,getdate())) --from previous month
and datepart(year,oh.date_despatched) = datepart(year,dateadd(year,0,getdate())) -- Added the Current year correct for 11 months but not for turn of year
The second part of this query works got 11 months of the year but not for the turn of the year. So it will work well for this year 2004 but would not for 2005 (only in the month where the previous year fall in a different year from the present month. How do I modify this part of the query to work only for the 12 months in the year and be able to know when the turn of the year is reached . If you notice it looks at the previous month. If the previous month is in the same year this query works. An urgent answer would be appreciated.
March 23, 2004 at 2:21 am
If you are looking to return just the items despatched during the last calendar month, try using something like this:
where oh.date_despatched between convert(char, dateadd(mm, -1, dateadd(dd, -(day(getdate()) - 1), getdate())), 112) and convert(char, dateadd(dd, -day(getdate()), getdate()), 112)
This calculates the 1st day of the preceeding month and the last day of the preceeding month with no reference to the year datepart and therefore will not be affected when the year rolls over.
March 23, 2004 at 8:54 am
Thank U very much. I think this will work. I cannot test it out until the turn of the year.
Cheers
Jesuseun
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply