[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Find previous month's data

neheyen

3/29/2007 4:28:00 PM

I have a need to find the data in the previous month. In other words, if ran
today, the where clause should include the date range of February 1, 2007 to
February 28, 2007. It should be automatic so that I can include it in a
scheduled DTS.

A previous dba left this snippet, but no one knows if it was ever tested and
it is beyond my understanding of SQL. Can someone take a look to see if it
will work?

WHERE (DETAIL_TYPE < 20)

AND (POST_DATE >=
CONVERT(datetime, CONVERT(varchar(2), MONTH(DATEADD(MM, - 1, GETDATE()))) +
'-' + CONVERT(varchar(2), DAY('01-dec-2000')) + '-' + CONVERT(varchar(4),
YEAR(DATEADD(MM, 1, GETDATE())))) + 0)

AND (POST_DATE <=
CONVERT(datetime, CONVERT(varchar(2), MONTH(DATEADD(MM, 0, GETDATE()))) +
'-' + CONVERT(varchar(2), DAY('01-dec-2000')) + '-' + CONVERT(varchar(4),
YEAR(DATEADD(MM, 1, GETDATE())))) - 1)

Thanks!
Norman
5 Answers

Tom Cooper

3/29/2007 4:47:00 PM

0

It uses an unsafe format for dates, is (at least to me) uneccessarily
complex and includes only midnight of the last day of the month. Use
something like

WHERE DETAIL_TYPE < 20
AND POST_DATE >= Select DateAdd(mm, DateDiff(mm, '20000101', GetDate()),
'19991201')
AND POST_DATE < Select DateAdd(mm, DateDiff(mm, '20000101', GetDate()),
'20000101')

Tom

"neheyen" <neheyen@discussions.microsoft.com> wrote in message
news:08451E67-B8E0-40C0-AA45-59580C97F0F7@microsoft.com...
>I have a need to find the data in the previous month. In other words, if
>ran
> today, the where clause should include the date range of February 1, 2007
> to
> February 28, 2007. It should be automatic so that I can include it in a
> scheduled DTS.
>
> A previous dba left this snippet, but no one knows if it was ever tested
> and
> it is beyond my understanding of SQL. Can someone take a look to see if it
> will work?
>
> WHERE (DETAIL_TYPE < 20)
>
> AND (POST_DATE >=
> CONVERT(datetime, CONVERT(varchar(2), MONTH(DATEADD(MM, - 1, GETDATE())))
> +
> '-' + CONVERT(varchar(2), DAY('01-dec-2000')) + '-' + CONVERT(varchar(4),
> YEAR(DATEADD(MM, 1, GETDATE())))) + 0)
>
> AND (POST_DATE <=
> CONVERT(datetime, CONVERT(varchar(2), MONTH(DATEADD(MM, 0, GETDATE()))) +
> '-' + CONVERT(varchar(2), DAY('01-dec-2000')) + '-' + CONVERT(varchar(4),
> YEAR(DATEADD(MM, 1, GETDATE())))) - 1)
>
> Thanks!
> Norman


Plamen Ratchev

3/29/2007 4:48:00 PM

0

This seems to be a very complex way to determine the prior month dates. Plus
it has a bug that it does not pull the last day of the month (in your
example Feb 28 is not included, unless that is a requirement). Here is a
faster and more simplified approach:

WHERE DETAIL_TYPE < 20
AND POST_DATE >= DATEADD(month, DATEDIFF(month, 0, getdate()) - 1, 0)
AND
AND POST_DATE < DATEADD(month, DATEDIFF(month, 0, getdate()), 0)

HTH,

Plamen Ratchev
http://www.SQL...

Plamen Ratchev

3/29/2007 4:54:00 PM

0

.... got one extra AND, should be:
WHERE DETAIL_TYPE < 20
AND POST_DATE >= DATEADD(month, DATEDIFF(month, 0, getdate()) - 1, 0)
AND POST_DATE < DATEADD(month, DATEDIFF(month, 0, getdate()), 0)

Plamen Ratchev
http://www.SQL...

ML

3/29/2007 4:56:00 PM

0

Take a look at this:
http://milambda.blo...2006/09/datetime-constructor-sql-...


ML

---
http://milambda.blo...

neheyen

3/29/2007 5:40:00 PM

0

Thanks for the excellent and prompt replies - I'll give these a try and see
if I can make them work in the rest of the script.

Many thanks!
Norman

"Plamen Ratchev" wrote:

> .... got one extra AND, should be:
> WHERE DETAIL_TYPE < 20
> AND POST_DATE >= DATEADD(month, DATEDIFF(month, 0, getdate()) - 1, 0)
> AND POST_DATE < DATEADD(month, DATEDIFF(month, 0, getdate()), 0)
>
> Plamen Ratchev
> http://www.SQL...
>
>
>
>