Skip to Content
author's profile photo Former Member
Former Member

retreives records one month prior

in a stored procedure i am trying to retrieve records from one month prior.

my results are not as i expected.

I would like all the records from the 1st of the previous month to the last day of the previous month.

all records created in november. when i use this syntax, i get records from

2008-10-29 00:00:00.000 through 2008-12-22 00:00:00.000

how do i acheive this in the stored proc, not the report.

en_remt_tbl.EN_REMT_CRTDT >=CONVERT(varchar(10),dateadd(mm, -1,dateadd(dd,-1*datepart(dd, getdate())-1, getdate())), 101)

and en_remt_tbl.EN_REMT_CRTDT <=CONVERT(varchar(10),dateadd(mm, 1,dateadd(dd,1*datepart(dd, getdate())+1, getdate())), 101)

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 23, 2008 at 03:57 PM

    Hi Sharon,

    This formula works for me. I have used getdate()

    select getdate(), DateAdd(dd,-1, Convert(smalldatetime, '01/'+ Convert(varchar,DatePart(mm,getdate()))+'/'+ Convert(varchar,DatePart(yyyy,getdate())),103)),
    Convert(smalldatetime, '01/'+ Convert(varchar,DatePart(mm,DateAdd(mm,-1,getdate())))+'/'+ Convert(varchar,DatePart(yyyy,DateAdd(mm,-1,getdate()))),103)

    Regards

    Alan

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 23, 2008 at 04:06 PM

    Hi Sharon

    Can you please tell us what results you get and what is missing? You may try using the 'LastFullMonth' function available in Crystal. Please provide us some more information.

    Regards

    Girish

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 23, 2008 at 04:08 PM

    Try this query

    select * from table where month(en_remt_tbl.EN_REMT_CRTDT)=month(getdate())-1 and year(en_remt_tbl.EN_REMT_CRTDT)=year(getdate())

    This query won't work if the current month is January so use if condition in the stored procedure like this

    if month(getdate())=1 then

    begin

    select * from table where month(en_remt_tbl.EN_REMT_CRTDT=12 and year(en_remt_tbl.EN_REMT_CRTDT)=year(getdate())-1

    end

    else

    begin

    select * from table where month(en_remt_tbl.EN_REMT_CRTDT)=month(getdate())-1 and year(en_remt_tbl.EN_REMT_CRTDT)=year(getdate())

    end

    hope this helps!

    Raghavendra

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 23, 2008 at 04:10 PM

    Hi,

    Please use the below query....

    select dateadd(dd,-1 * datepart(dd,getdate()) ,getdate())

    select dateadd(dd,-1 * datepart(dd,dateadd(dd,-1 * datepart(dd,getdate()) ,getdate())) + 1 ,dateadd(dd,-1 * datepart(dd,getdate()) ,getdate()))

    Regards,

    Ganesh Kumar.V

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.