1

I´m new on this site and with SQL, so apologies for my humble question. I have written reports in SQL Server 2012 environment. The problem is, how get a dynamic timeline to work in the header of the report? Reports are run at the first and 16th day of a month.

The separate selects inside the header row are working, but I do not find a decent and informative solution for the header. The idea is to show the reported period dynamically, like '2017-02-01 - 2017-02-15'.

This code:

N'<H1>Timeline'+(select DATEADD(dd, 1, EOMONTH(getdate(), -1)))+'-'
  +(select DATEADD(dd, 15, EOMONTH(getdate(), -1)))+'</H1>'+

Causes an error:

Msg 402, Level 16, State 1, Line 11
The data types nvarchar and date are incompatible in the add operator.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hande_57
  • 11
  • 1
  • `DATEADD()` returns an integer - you need to convert it to a string first (e.g. `CONVERT(varchar(11), DATEADD(...))`). Also, please don't use shorthand like `dd` - you saved **a whole character** but what did you gain? Try that with `W` or `Y` to see [why I caution against lazy shorthand](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations.aspx). – Aaron Bertrand Feb 27 '17 at 22:17
  • Also to avoid `EOMONTH` and all those `-1` things, how about `DECLARE @d date = GETDATE(); SELECT DATEADD(DAY,1-DAY(@d),@d), DATEADD(DAY,16-DAY(@d),@d);` – Aaron Bertrand Feb 27 '17 at 22:20
  • Interesting answer! I have to test these elements and different scenarios. There seems to be a lot of sql studies waiting in the future. Thank you for your answer! – Hande_57 Feb 27 '17 at 22:50

0 Answers0