Syntax issue determine 1 day of month based on calculation

Question

I disagree - I am looking for the correct syntax to change the where clause to first return the results of the first part of the where clause then return the first of the month of that date result. Need help with the correct syntax.

thanks,

K

-- DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

Modify the Where statement

Blockquote

CAST([PKDate] AS DATE) > CAST(GETDATE() - 91 AS DATE)

Blockquote

to always reflect the first day of the month for the results of the 91 day calculation - ie. if the date of 91 days back = 3/10/2016, then the actual date should be 3/1/2016

SELECT [PKDate]
, [calendar_MONTH] as MthNo
, [is_weekend]
, [is_workday]
, Day(EOMONTH([PKDate])) as DaysinMth
FROM [calendar].[dbo].[days]
WHERE   CAST([PKDate] AS DATE) > CAST(GETDATE() - 91 AS DATE)
AND CAST([PKDate] AS DATE) <= CAST(GETDATE() - 1 AS DATE)
GROUP BY [PKDate], calendar_MONTH
, is_weekend
, [is_workday]

Possible duplicate of [How can I select the first day of a month in SQL?](http://stackoverflow.com/questions/1520789/how-can-i-select-the-first-day-of-a-month-in-sql) — Tab Alleman, Jun 08 '16 at 19:36
I disagree, my question is how do I format the where clause to pass the correct first of the month date on the results of the 91 days back calculation? — Karen Schaefer, Jun 08 '16 at 19:49
And you don't see how being able to get the first day of the month for the date 91 days ago would solve that problem? — Tab Alleman, Jun 08 '16 at 19:50

score 0 · Answer 1 · answered Jun 08 '16 at 19:57

Nevermind I got it!!!

SELECT [PKDate]
, a.[calendar_month] as MthNo
, [is_weekend]
, [is_workday]
, Day(EOMONTH([PKDate])) as DaysinMth
, DATEADD(mm, DATEDIFF(mm, 0, CAST(GETDATE() - 91 AS DATE)), 0) as AdjDate
FROM [calendar].[dbo].[days] a
WHERE   CAST([PKDate] AS DATE) > DATEADD(mm, DATEDIFF(mm, 0, CAST(GETDATE() - 91 AS DATE)), 0)
    AND CAST([PKDate] AS DATE) <= CAST(GETDATE() - 1 AS DATE)
GROUP BY [PKDate]
    ,   a.calendar_MONTH
    ,   is_weekend
    ,   [is_workday]

score 0 · Accepted Answer · answered Jun 08 '16 at 20:01

0

This might even be simpler...

DATEADD(DAY, -91, GETDATE())

answered Jun 08 '16 at 20:01

Heinrich Smit

68
1
9

Syntax issue determine 1 day of month based on calculation

2 Answers2