1

I have an SSRS report that queries the AS400. The query takes 2 parameters, start_date and end_date. So the report has two text type parameters.

I am trying to take today's date (using the Today() function), and do a DateDiff of 60 days, and use that value as the default value.

Here are the complications:

  1. My Start_Date and End_Date parameter in the dataset are integer type variables, not DateTime.
  2. That means, I cannot do a DateAdd("d", -60, Today()). Because then, that parameter is considered a date parameter, not an integer parameter. Passing that value to the query is not possible.
  3. I cannot change the parameters' data type to Date. Again, the query is expecting an integer value. This is sending a date value (with / and everything).
  4. I cannot cast the parameter in the query, because AS400 parameters are a PAIN to pass. So right now, the parameter is a ?. There are 6 parameters in the query and are in the exact order as they are in the report. I don't know how a CAST or a CONVERT is going to work on a ?.
  5. The Start_Date and End_Date fields have this format: YYYYMMDD

Here is what I was thinking about doing:

  1. I was thinking of doing a CAST on today's year, today's month, and today's date to STR and then concatenate them all.
  2. And then, CAST that STR to INT and adding -60.

But if I did that, I am left with a completely random number. It may be anything. For instance, today's date minus 60 days would be 20170650, which is obviously not a valid date.

How do I do this? Do I get today's date, add -60 days, then convert it to Integer? When I try to do that, I get a greyed out empty parameter textbox when I run the report. I thought that would work, I don't know why it does not.

Any suggestions would be appreciated.

TL;DR:
How do I do a DateAdd of -60 days to Today's date and convert it to YYYYMMDD format and pass it as an Integer to the query?

Crazy Cucumber
  • 479
  • 8
  • 36
  • 1
    `CAST`/`CONVERT` works just fine - the question mark is a _parameter marker_, which is replaced at eval time with the appropriate data. If you're passing something in to a query (DB2, or any RDBMS), you **must** use them, or fall victim to SQL Injection. – Clockwork-Muse Jul 10 '17 at 23:25
  • Yes, but when I do it with SQL Server, I usually create a variable and use the variable as a parameter. That does not seem to be a possibility on DB2. – Crazy Cucumber Jul 11 '17 at 13:51
  • Doesn't matter; even your SQL Server variables are parameter markers, just friendlier ones (and some drivers for DB2 actually do permit named parameter markers). – Clockwork-Muse Jul 11 '17 at 15:54
  • That is very helpful, thank you. The question mark was really scary to me. I will try messing with it a bit. That might make this whole thing easier on me. – Crazy Cucumber Jul 11 '17 at 18:38

3 Answers3

1

This is all done using SSRS functions. It might be easier and/or cleaner to write a function within your report to do this but I'll leave that to you.

This takes the current date, does a DATEADD of minus 60 days and picks the year, month and day from the result. It multiplies the year by 10000, the month by 100 and adds the day. You end up with an integer in the format YYYMMDD

Here's the expression (split onto a few lines for clarity)

=
(YEAR(dateadd(DateInterval.Day, -60, now())) * 10000) 
+ (Month(dateadd(DateInterval.Day, -60, now())) *100) 
+ day(dateadd(DateInterval.Day, -60, now()))
Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • This seems like the way I'd like to go, I will try it out and let you know if it works. Thank you. – Crazy Cucumber Jul 11 '17 at 13:48
  • Can you think of any reasons why the entire parameter section disappears when I add your query as the default value of a parameter? I mean, there is a section on the top with the "View Report" button, but it does not show me any parameters – Crazy Cucumber Jul 11 '17 at 19:16
  • https://stackoverflow.com/questions/2541456/ssrs-date-default-with-formula-disables-parameter - WOW! That is apparently a "feature" of SSRS. I prefer the term BUG. – Crazy Cucumber Jul 11 '17 at 20:59
  • I've just tested this. I created a blank report added a single parameter, set the type to integer and set the default value expression to be the exact code above. When I run the report. The parameter appeared as expected with the correct value showing. I'm not sure why you don;t see the same. Try craeting a new report as I did to see if you can replicate it. – Alan Schofield Jul 11 '17 at 20:59
  • It was because I had more than one parameter and the date value default parameters are in the 3rd and 4th position. If there is a non-default parameter BEFORE a default parameter, SSRS expects you to enter a value for the non-default parameter before populating a value for the default. This is such a stupid design choice by Microsoft. – Crazy Cucumber Jul 11 '17 at 21:02
  • Wow, I've been writing reports in SSRS for years, and some pretty complex ones but I've never come across that, or maybe I got lucky several hundred times :) – Alan Schofield Jul 11 '17 at 21:03
1

U can do :

select cast(replace(char(current_date - 60 day, ISO),'-','')as integer) from *your_table*

As you can see, the format is an integer, and you get : yyyyMMdd, u can after susbtract or add what you want.

Cass
  • 144
  • 1
  • 1
  • 13
0

The SQL way to do so would be

   year(current_date - 60 days) * 10000
    + month(current_date - 60 days) * 100  
    +   day(current_date - 60 days)

Personally, I prefer to have just a conversion function that takes a date and returns a numeric representation.

select * 
from mytable
where trndte 
  between ConvertToDte(current_date - 60 days)
      and ConvertToDte(current_date)

In fact, I usually use Alan Campin - IDATE - iSeries Date Conversion tool, which is a set of UDFs the convert between date data types and numeric or character.

Charles
  • 21,637
  • 1
  • 20
  • 44