Need records forthe last month

22 Apr 2006 10:31 PM

SK

I need to retrieve records that are posted within the last month and I've
tried variations of below, but do not get all the desired result. I either
get records outside the range or not all the records I should. Posted is
Date1 below.

select convert(char,date1,101)as date1,convert(char,date2,101)as date2 from
table1 WHERE DATEDIFF( month, date1 , GETDATE() ) <=1

select convert(char,date1,101)as date1,convert(char,date2,101)as date2 from
table1 where date1 <= dateadd(month, -1, getdate())

It seems it should be very simple, but I haven't found the satisfactory
result.
I've been spending a lot of time on this and would appreciate some ideas.

22 Apr 2006 11:01 PM

Colin Dawson

You don't explain exactly what is wrong with the result sets, but I'll
hazard a guess. GETDATE() returns both a date and a time part. So DateDiff
and date add will retain these, normally this is the desired operation but
in your case, I'd guess that you want all the rows in the last month, that
is everything after midnight (12:00am) on the given day.   I'd suggest that
you do this like this...

select
convert(char,date1,101)as date1, convert(char,date2,101) as date2
from table1
where date1 <= dateadd(month, -1, dateadd( day, datediff( day, 0,
getdate() ), 0 ) )

It's like your second attempt, but with one difference, the way that getdate
is read.

The code dateadd(day, datediff( day, 0, getdate()),0)   will perform a
GetDate() which is effectivly now, then strip off the time part to return
12:00am.   Will return all rows which were returned in the last month, but
also including anything from the entire day of the first day in the range.

Regards

Colin Dawson
www.cjdawson.com

Show quoteHide quote

"SK" <S*@discussions.microsoft.com> wrote in message
news:644FB09F-2EA2-434C-B4DC-D5BE6E46936A@microsoft.com...
>I need to retrieve records that are posted within the last month and I've
> tried variations of below, but do not get all the desired result. I
> either
> get records outside the range or not all the records I should. Posted is
> Date1 below.
>
> select convert(char,date1,101)as date1,convert(char,date2,101)as date2
> from
> table1 WHERE DATEDIFF( month, date1 , GETDATE() ) <=1
>
>
> select convert(char,date1,101)as date1,convert(char,date2,101)as date2
> from
> table1 where date1 <= dateadd(month, -1, getdate())
>
> It seems it should be very simple, but I haven't found the satisfactory
> result.
> I've been spending a lot of time on this and would appreciate some ideas.
>
>
>
>

23 Apr 2006 2:44 AM

SK

Thank you so much for answering on a Saturday.

Yes, I wanted all the dates within the last month. After spending all day
on it and getting very confused, I finally found something that seems to
work. I created a date table with sample dates and compared very closely.
It was never quite accurate. I tried the code, but it returns records from
2005, which is a problem I was having also.

But, finally this seems to work.

select convert(char,date1,1) as date1
from table1
where date1 <= getdate() and date1 >=
convert(char,dateadd(month,-1,getdate()),1)

Thanks again,
SK

Show quoteHide quote

"Colin Dawson" wrote:

> You don't explain exactly what is wrong with the result sets, but I'll
> hazard a guess. GETDATE() returns both a date and a time part. So DateDiff
> and date add will retain these, normally this is the desired operation but
> in your case, I'd guess that you want all the rows in the last month, that
> is everything after midnight (12:00am) on the given day.   I'd suggest that
> you do this like this...
>
>
> select
>   convert(char,date1,101)as date1, convert(char,date2,101) as date2
> from table1
> where date1 <= dateadd(month, -1, dateadd( day, datediff( day, 0,
> getdate() ), 0 ) )
>
> It's like your second attempt, but with one difference, the way that getdate
> is read.
>
> The code dateadd(day, datediff( day, 0, getdate()),0)   will perform a
> GetDate() which is effectivly now, then strip off the time part to return
> 12:00am.   Will return all rows which were returned in the last month, but
> also including anything from the entire day of the first day in the range.
>
> Regards
>
> Colin Dawson
> www.cjdawson.com
>
> "SK" <S*@discussions.microsoft.com> wrote in message
> news:644FB09F-2EA2-434C-B4DC-D5BE6E46936A@microsoft.com...
> >I need to retrieve records that are posted within the last month and I've
> > tried variations of below, but do not get all the desired result. I
> > either
> > get records outside the range or not all the records I should. Posted is
> > Date1 below.
> >
> > select convert(char,date1,101)as date1,convert(char,date2,101)as date2
> > from
> > table1 WHERE DATEDIFF( month, date1 , GETDATE() ) <=1
> >
> >
> > select convert(char,date1,101)as date1,convert(char,date2,101)as date2
> > from
> > table1 where date1 <= dateadd(month, -1, getdate())
> >
> > It seems it should be very simple, but I haven't found the satisfactory
> > result.
> > I've been spending a lot of time on this and would appreciate some ideas.
> >
> >
> >
> >
>
>
>

Thread options