Query Date Help

Question

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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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