Query Date Help

• mbrady5

  SSCrazy

  Points: 2835

  More actions

  June 2, 2017 at 6:45 am

  #337445

  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: 148809

  More actions

  June 2, 2017 at 7:07 am

  #1945089

  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

  More actions

  June 2, 2017 at 7:22 am

  #1945097

  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: 148809

  More actions

  June 2, 2017 at 7:35 am

  #1945102

  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

  More actions

  June 2, 2017 at 8:00 am

  #1945106

  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