Query Date Help

  • mbrady5

    SSCrazy

    Points: 2835

    Hi, I have a query that is looking at the current Month. My issue is that there is a gap if it's the 1st of the Month.
    I'd like to add a condition that will tell the query to include the prior day but, only if it's the first of the month.
    Here is my current where clause that I use to look at the current MTD.
    AND dbo.manifests.manifest_date >= DATEADD(MONTH,
                     DATEDIFF(MONTH, 0,
                        CURRENT_TIMESTAMP),
                     0)

    Thanks so much

  • John Mitchell-245523

    SSC Guru

    Points: 148652

    AND dbo.manifests.manifest_date >= DATEADD(DAY,CASE WHEN DAY(manifest_date)=1 THEN -1 ELSE 0 END, DATEADD(MONTH,
          DATEDIFF(MONTH, 0,
           CURRENT_TIMESTAMP),
          0)

    You can also use the SIGN function instead of the CASE expression if you prefer.

    John

  • mbrady5

    SSCrazy

    Points: 2835

    Thanks so much John..
    I made a minor change but wnat to be 100% confident. Can you take a look?

    Original
    AND dbo.manifests.manifest_date >= DATEADD(DAY,CASE WHEN DAY(manifest_date)=1 THEN -1 ELSE 0 END, DATEADD(MONTH,
       DATEDIFF(MONTH, 0,
       CURRENT_TIMESTAMP),
       0)

    Minor Change..ManifestDate to Getdate()
    AND dbo.manifests.manifest_date >= DATEADD(DAY,CASE WHEN DAY(GETDATE())=1 THEN -1 ELSE 0 END, DATEADD(MONTH,
       DATEDIFF(MONTH, 0,
       CURRENT_TIMESTAMP),
       0)

  • John Mitchell-245523

    SSC Guru

    Points: 148652

    Ah yes, I should have spotted that - you're basing it on the current date, not on any value in the column.  Yes, that should work.  My only advice would be to be consistent in your use of GETDATE() or CURRENT_TIMESTAMP.  Choose one and stick with it.

    John

  • mbrady5

    SSCrazy

    Points: 2835

    John Mitchell-245523 - Friday, June 2, 2017 7:35 AM

    Ah yes, I should have spotted that - you're basing it on the current date, not on any value in the column.  Yes, that should work.  My only advice would be to be consistent in your use of GETDATE() or CURRENT_TIMESTAMP.  Choose one and stick with it.

    John

    Thanks so much! Have a great weekend

Viewing 5 posts - 1 through 5 (of 5 total)

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