1

We've been using MS Access, with the following syntax for MTD Data that works for us:

Between DateSerial(Year(Date()),Month(Date()),1) 
    And DateSerial(Year(Date()),Month(Date())+1,0)

We need to transition the above logic to SQL/SSRS for automatic emailed reports, but I cannot get this DateSerial logic to work with SQL.

In the Filter field of the SQL query, I can successfully use BETWEEN '8/1/2014' AND '8/31/2014' for MTD data, but would like to have a DateSerial logic applied so that reports don't need to be created for every month, quarter, year, etc.

When trying to use the DateSerial function, we get the error "Invalid or missing Expression". I've seen a few topics on this that Parameters are required, but really believe that this is a simple syntax issue for the filter field, since actual dates work with the BETWEEN command.

crthompson
  • 15,653
  • 6
  • 58
  • 80
ITSP
  • 23
  • 2
  • 5

3 Answers3

0

You can use the function CONVERT:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Or the function DATEFROMPARTS if you are using SQL Server 2012:

http://msdn.microsoft.com/en-us/library/hh213228.aspx

Or DATEADD:

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0); -- first day of current month
select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1) -- last day of current month

This last one I took from: https://stackoverflow.com/a/11746042/1274092

See mine at:

http://sqlfiddle.com/#!3/d41d8/38333

Community
  • 1
  • 1
Caffé
  • 1,161
  • 1
  • 9
  • 19
  • This needs to be a WHERE or HAVING clause. I tried WHERE... BETWEEN DATEADD("MONTH", DATEDIFF("MONTH", 0, GETDATE()) - 1, 0) AND DATEADD("MONTH", DATEDIFF("MONTH", - 1, GETDATE()) - 1, - 1)). This returned: Error [42703] Error: column "MONTH" does not exist. We are using SQL Server 2008 – ITSP Aug 27 '14 at 20:17
  • Could this be from the ODBC driver? Error Source: PSQLODBC.DLL – ITSP Aug 27 '14 at 20:21
  • 1
    @ITSP And what outcomes did you get? Note that you mustn't use quotes in "MONTH" as it's a kind of SQL Server constant. – Caffé Aug 27 '14 at 20:21
  • SQL automatically added the quotes around MONTH. This returned: Error [42703] Error: column "MONTH" does not exist. – ITSP Aug 27 '14 at 20:29
  • @ITSP Should have worked. I've updated my answer just to change it to the current month and also to point to a functional example tested in SQL Server 2008. What SQL version are you using? – Caffé Aug 27 '14 at 20:46
  • SQL Server 2008. This is the Filter based on your revised date rang (SQL added the quotes again): BETWEEN DATEADD("MONTH", DATEDIFF("MONTH", 0, GETDATE()), 0) AND DATEADD("MONTH", DATEDIFF("MONTH", - 1, GETDATE()), - 1) – ITSP Aug 27 '14 at 20:52
  • Error Source: PSQLODBC.DLL Error Message: ERROR [42703] ERROR: column "MONTH" does not exist – ITSP Aug 27 '14 at 20:53
  • @ITSP SQL server management studio doesnt just add quotes. What are you using that is quoting that value? Check the examples [on msdn](http://msdn.microsoft.com/en-us/library/ms186819.aspx) and you'll see that there are no quotes – crthompson Aug 27 '14 at 20:59
  • Visual Studio 2008. The quotes are being automatically added. – ITSP Aug 27 '14 at 21:04
  • @ITSP Maybe a particularity of SSRS. Try "m" or m instead of MONTH. – Caffé Aug 27 '14 at 21:04
  • @ITSP Do you have SQL Server Management Studio? You could try it there just to see if it works. – Caffé Aug 28 '14 at 00:54
  • @Caffe' This has been resolved. The ODBC driver does not apparently play well with SSRS. The DateSerial command would not work within the query itself. The workaround was to add the filter to the Dataset. This syntax is what works, but again only in the Dataset filter: [expression] Between [first value box] =DateSerial(Year(Now()),1,1) [second value box] =DateSerial(Year(Now()),12,31) – ITSP Sep 04 '14 at 17:54
  • This example give the YTD logic for the reporting that we require – ITSP Sep 04 '14 at 17:55
0

There are several different ways to get this. Here is just one way.

  1. Get todays date. In this case 8/27/2014

    Declare @Today date = cast(getdate() as date)

  2. Get the first of the month, 26 days in the past

    Declare @StartDate date = dateadd(d, -1 * (day(@Today) - 1), @Today)

  3. select @Today, @StartDate

crthompson
  • 15,653
  • 6
  • 58
  • 80
CandiedCode
  • 554
  • 1
  • 3
  • 7
0

This has been resolved. The ODBC driver does not apparently play well with SSRS. The DateSerial command would not work within the query itself. The workaround was to add the filter to the Dataset. This syntax is what works, but again only in the Dataset filter: [expression] Between [first value box] =DateSerial(Year(Now()),1,1) [second value box] =DateSerial(Year(Now()),12,31)

This gives us the YTD reporting data that we require.

ITSP
  • 23
  • 2
  • 5