1

I have an SSRS report which uses a @Year parameter, which is chosen by the user at run-time.

Fine, so far, but the SQL in the Data set Properties section contains a hard-coded date of '2010-08-31' , but, the year part of it needs to be the same as the @Year parameter which the user chooses. In other words, if you run the report in the year 2010 the results will be correct, but not if you run it now (in 2014).

The SQL at the moment is (miminum required):

SELECT  DateDiff(Year, birth_dt, '2010-08-31')
--Date of Start of Academic Term
FROM    table99
WHERE  acad_period = @Year

...so my question is, what is the correct syntax for substituting the @Year value in place of '2010'?

EDIT : Please note that the actual format for the year is (eg) 12/13, 13/14

user3293484
  • 11
  • 4
  • I should point out that your age calculation is flawed. `DateDiff()` returns the difference in years, irrespective of date. http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate – JC Ford Feb 10 '14 at 16:31

3 Answers3

0

you can replace the line

SELECT DateDiff(Year, birth_dt, '2010-08-31')

with

SELECT DateDiff(Year, birth_dt, @Year+'-08-31')

To do the same with current date

SELECT DateDiff(Year, birth_dt, DATEPART(yyyy, getdate())+'-' + DATEPART(mm, getdate()) +'-'+DATEPART(dd, getdate()))
Miller
  • 1,096
  • 9
  • 22
0

based on your clarification in comment, if you pass in '12/13' your query would be something like this.

SELECT DATEDIFF(Year, birth_dt, '20'+LEFT(@Year,2) + '-08-31')
FROM    table99
WHERE  acad_period = @Year
0

Since your year parameter isn't a simple year value but is instead a string like "13/14" presumably meaning the 2013/2014 school year, I would definitely handle parsing it outside of the query.

Add a computed @TermStart parameter to the dataset with the following formula:

=DateSerial(2000 + CInt(Split(Parameters!Year.Value,"/")(0)),8,31)

(So long as you aren't expecting any dates prior to 2000 of course)

Then you can use the @Year and @TermStart parameters in the query like so:

SELECT  DateDiff(Year, birth_dt, @TermStart)
--Date of Start of Academic Term
FROM    table99
WHERE  acad_period = @Year

But as I mentioned in a comment above, that is not the correct way to calculate age. There are several ways to do that. My favorite is this:

SELECT  datediff(hour,birth_dt,@TermStart)/8766
--Date of Start of Academic Term
FROM    table99
WHERE  acad_period = @Year
JC Ford
  • 6,946
  • 3
  • 25
  • 34
  • Thank you for your answer, but that does not work. The @Year parameter is actually a string, eg 12/13, 13/14 etc. My apologies - my fault - I should have made that clear in the initial post. – user3293484 Feb 10 '14 at 16:41
  • In that case, the formula would be `=DateSerial(CInt(Parameters!Year.Value),8,31)`. In any case, concatenating in the query will work fine. I try to avoid altering parameters in my query because I feel it makes it more difficult to understand. Altering in a separate parameter allows me to convey the meaning of the alteration with the parameter name. – JC Ford Feb 10 '14 at 16:46
  • Oh I see now the year parameter is not actually just a year. I'll update my answer. – JC Ford Feb 10 '14 at 16:50
  • Thanks again, but I'm not sure how to add a computed parm (I'm a newb at this) ... can it be used in the SQL, similar to the SELECT statement you originally posted?) Thanks again! – user3293484 Feb 10 '14 at 16:57
  • You can add it as a Report Parameter instead if you like. Just mark it as hidden and internal so the user won't be prompted. Make sure it's below the @Year parameter in the list. Put the expression in the Default Value for the parameter. And use it in the query just like any other parameter. – JC Ford Feb 10 '14 at 17:07
  • Thanks for the reply. I have opted for the : **datediff(hour,birth_dt,@TermStart)/8766** option, and it works well. . A big thanks too, to all the other posters who were kind enough to help with their replies too. – user3293484 Feb 11 '14 at 15:55