February 28, 2017 at 7:45 am
I have a report that shows month to date data. I need a text box to show the following.......
1.on any day of the month show the first day of the month.
2.on the first day of the next month, show the first day of the previous month.
3.So, if today is 2/28/17, show 2/1/17. Tomorrow show 2/1/17. Then on 3/2/17, show 3/1/17.
February 28, 2017 at 7:54 am
SELECT DATEADD(month, DATEDIFF(month, 0, getdate()), 0) AS StartOfMonth
Is this what you require?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 28, 2017 at 8:07 am
I need the expression to test if "Today" is the first day of the month. So, on March 1st, I actually need to show the user the date range of the report. On March 1st, the report is showing data for the month of February, 2/1 - 2/28.
February 28, 2017 at 8:08 am
Henrico Bekker - Tuesday, February 28, 2017 7:54 AMSELECT DATEADD(month, DATEDIFF(month, 0, getdate()), 0) AS StartOfMonthIs this what you require?
That will return 01 March 2017 tomorrow. This will resolve that:SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())), 0) AS StartOfMonth
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 28, 2017 at 8:15 am
Thanx.
February 28, 2017 at 8:25 am
If you're on SQL Server 2012, you can use either of these. You need the "-1" so that it gives the beginning of the previous month on the first of the month. I tested both on a seven million row table and performance was almost identical.SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()-1), 0) AS StartOfMonth
SELECT DATEFROMPARTS(YEAR(GETDATE()-1),MONTH(GETDATE()-1),1) AS StartOfMonth
I suspect you may have been looking for an expression in Reporting Services, though - is that right? If so, the first example above should be fairly easy to translate into SSRS expression language. You'll want to consider whether it's more efficient to calculate these values in your query or in your presentation layer.
John
February 28, 2017 at 8:33 am
Hadn't noticed this was SSRS (after seeing the first answer). An SSRS expression would be:=DATEADD("M", DATEDIFF("M", "01/01/2000", DATEADD("d", -1, TODAY())), "01/01/2000")
Effectively, like John said, the same, but SSRS doesn't recognise "0" as a date.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply