First day 3 month ago ( in Tsql)

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

First day 3 month ago ( in Tsql)

Author

Topic

Pgrandjean
Starting Member

5 Posts

Posted - 2009-06-09 : 14:22:57

Hi.

I am creating a view showing invoices grouped by duedate.
(Later on I will call this view from an Pivot table in Excel 2007)
I am using MS Dynamics Ax on an sql 2005 base.
The code would be like:

select
invoice, amountmst, duedate
from custtrans
where :

I would like to have four periods:

1. After this month.
month(duedate)>month(getdate()) and year(duedate)>=year(getdate())
This will not work, because duedates in e.g. March next year will not be included. HELP !

2. This month.
month(duedate)=month(getdate()) and year(duedate)=year(getdate())

3. The last 3 months.
and
4. Older 3 months.

My problem is : How do I find the first day of the month 3 months ago ?
Today is June 11. In "3" I need all invoices from March, April and May this year. In "4" I need all invooces due before March this year.

Please advice

Med venlig hilsen
Peter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 14:27:16

3. where date >= dateadd(mm,datediff(mm,0,getdate())-3,0)
and date <dateadd(mm,datediff(mm,0,getdate()),0)

4.date <dateadd(mm,datediff(mm,0,getdate())-3,0)

Pgrandjean
Starting Member

5 Posts

Posted - 2009-06-09 : 15:09:27

Hi

Thank you very much

Peter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-10 : 13:55:31

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources