June 2, 2017 at 6:45 am
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
June 2, 2017 at 7:07 am
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
June 2, 2017 at 7:22 am
Thanks so much John..
I made a minor change but wnat to be 100% confident. Can you take a look?
OriginalAND 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)
June 2, 2017 at 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
June 2, 2017 at 8:00 am
John Mitchell-245523 - Friday, June 2, 2017 7:35 AMAh 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy