Last Three Full Months?

Hi,
I have sales line item detail like
invoicedate, custid, ordernum
Is there a way using SQL to only return records for the last three full
months? For example, if today is 2/4/05, I would want records with
invoicedate between 11/1/04 and 1/31/05.
TIA,
DanBreak down the problem. First, you want the first day of the current month:
convert (char 6), getdate, 112) + '01'
Next, you want from 3 months previous, up to but not including the first of
this month. The 3 months previous are:
dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
Now, put these into your WHERE clause:
WHERE
MyDate > = dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
and MyDate < convert (char 6), getdate, 112) + '01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dan" <dan@.nospam.com> wrote in message
news:IFmne.496$Wk4.8376@.news.uswest.net...
Hi,
I have sales line item detail like
invoicedate, custid, ordernum
Is there a way using SQL to only return records for the last three full
months? For example, if today is 2/4/05, I would want records with
invoicedate between 11/1/04 and 1/31/05.
TIA,
Dan|||declare @.date smalldatetime
declare @.start smalldatetime
declare @.end smalldatetime
set @.date = '2004-02-04'
set @.start = dateadd(d, -(datepart(d, @.date)) + 1, dateadd(m, -3, @.date))
set @.end = dateadd(d, -(datepart(d, @.date)) + 1, @.date)
select ... from invoice where invoiceDate >= @.start and invoiceDate < @.end
"Dan" <dan@.nospam.com> wrote in message
news:IFmne.496$Wk4.8376@.news.uswest.net...
> Hi,
> I have sales line item detail like
> invoicedate, custid, ordernum
> Is there a way using SQL to only return records for the last three full
> months? For example, if today is 2/4/05, I would want records with
> invoicedate between 11/1/04 and 1/31/05.
> TIA,
> Dan|||On 6/1/2005 1:10 PM, Tom Moreau wrote:
> Break down the problem. First, you want the first day of the current mont
h:
> convert (char 6), getdate, 112) + '01'
> Next, you want from 3 months previous, up to but not including the first o
f
> this month. The 3 months previous are:
> dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> Now, put these into your WHERE clause:
> WHERE
> MyDate > = dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> and MyDate < convert (char 6), getdate, 112) + '01'
In my query tool (Brio) I am getting invalid syntax error. Can you
double check syntax as I am not familiar with these commands? BTW what
does 112 signify?
Thanks,
Dan|||On 6/1/2005 1:22 PM, Dan wrote:
> On 6/1/2005 1:10 PM, Tom Moreau wrote:
>
>
> In my query tool (Brio) I am getting invalid syntax error. Can you
> double check syntax as I am not familiar with these commands? BTW what
> does 112 signify?
> Thanks,
> Dan
>
I fixed the code to run now. What does + '01' do?
Thanks,
Dan|||Sorry about that. I'm a little distracted by back pain right now. Try
this:
WHERE
MyDate > = dateadd (mm, -3, convert (char (6), getdate(), 112) + '01')
and MyDate < convert (char (6), getdate(), 112) + '01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dan" <dan@.nospam.com> wrote in message
news:E1nne.498$Wk4.9251@.news.uswest.net...
On 6/1/2005 1:10 PM, Tom Moreau wrote:
> Break down the problem. First, you want the first day of the current
> month:
> convert (char 6), getdate, 112) + '01'
> Next, you want from 3 months previous, up to but not including the first
> of
> this month. The 3 months previous are:
> dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> Now, put these into your WHERE clause:
> WHERE
> MyDate > = dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> and MyDate < convert (char 6), getdate, 112) + '01'
In my query tool (Brio) I am getting invalid syntax error. Can you
double check syntax as I am not familiar with these commands? BTW what
does 112 signify?
Thanks,
Dan|||The conversion to char (6) gives you the year and month only in YYYMM
format. You still need a day, and you supply that by concatenating '01'.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dan" <dan@.nospam.com> wrote in message
news:1lnne.499$Wk4.9860@.news.uswest.net...
On 6/1/2005 1:22 PM, Dan wrote:
> On 6/1/2005 1:10 PM, Tom Moreau wrote:
>
>
> In my query tool (Brio) I am getting invalid syntax error. Can you
> double check syntax as I am not familiar with these commands? BTW what
> does 112 signify?
> Thanks,
> Dan
>
I fixed the code to run now. What does + '01' do?
Thanks,
Dan|||On 6/1/2005 1:51 PM, Tom Moreau wrote:
> The conversion to char (6) gives you the year and month only in YYYMM
> format. You still need a day, and you supply that by concatenating '01'.
>
Thanks a bunch!
Dan|||Another alternative:
SELECT DATEADD( m, -3, @.dt - DAY( @.dt ) + 1 ) AS "start",
@.dt - DAY( @.dt ) AS "end"
-- where @.dt is the date value in question
Anith