Post reply

This Year last 2 months query

  • September 5, 2012 at 8:30 am

    #281217

    Hi all I am trying to get all Docket_Date (DateTime Column) to return all in this year for that are 2 months old.

    Here is what I have tried but still returns dates from earlier than 2 months

    Where did I go wrong

    WHERE (DATEPART(YEAR,GETDATE()) = DATEPART(yyyy, Docket_Date))And Docket_Date < DATEADD(MONTH, -2, GETDATE())

    thanks

  • September 5, 2012 at 8:33 am

    #1533864

    WHERE (DATEPART(YEAR,GETDATE()) = DATEPART(yyyy, Docket_Date))And Docket_Date > DATEADD(MONTH, -2, GETDATE())

    Woops

    Sorry

  • September 5, 2012 at 8:37 am

    #1533872

    So what are you looking for, dockets that are more than two months old, less than two months old, or exactly two months old? Not sure whether your second post indicates that you solved the problem yourself?

    John

  • September 5, 2012 at 8:41 am

    #1533877

    Hi John, Dockets that are 2months old, so 2months from today ?

  • September 5, 2012 at 8:54 am

    #1533890

    jerome.morris (9/5/2012)

    Hi John, Dockets that are 2months old, so 2months from today ?

    You know what would really help? Show us what you are trying to accomplish. Please post the DDL for the table, some sample data (as INSERT INTO statements), and the expected results based on the sample data. This will make things much clearer to us and others that may come along later.

    Also, make sure the sample data is just that, sample data; not live production data.

  • September 5, 2012 at 8:59 am

    #1533895

    I'm still not entirely clear what you mean, but if you mean what I think you mean, this should get you started:

    DECLARE

    @Now datetime

    , @Startdate date

    , @Enddate date

    SELECT

    @Now = CURRENT_TIMESTAMP

    SELECT -- startdate is date portion of two months ago

    @Startdate = CAST(DATEADD(m,-2,@Now) AS date)

    SELECT -- enddate is one day after start date

    @Enddate = DATEADD(d,1,@Startdate)

    SELECT

    ...

    WHERE

    Docket_Date >= @Startdate

    AND

    Docket_Date < @Enddate

    Now, what do you want to happen where this is run in January or February and therefore two months ago is last year?

    John

  • September 5, 2012 at 9:03 am

    #1533899

    For what I understood

    SELECT ...

    WHERE Docket_Date >= DATEADD( mm, DATEDIFF( MM, 0, GETDATE()) - 2, 0) --Last two months

    AND Docket_Date >= DATEADD( yyyy, DATEDIFF( yyyy, 0, GETDATE()), 0) -- This ensures you get this year only

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply