Using parameter date year to search for jobs beginning with "2020"

ERP 10
1

SQL/BAQ newbie here. Trying to use a parameter date year as the prefix in a BAQ looking for all jobs starting with 2020. I’m using the calculated field editor in my BAQ and have it working correctly as long as I enter 2020 into the calculated field code directly. I want to extract the year from a start date search parameter so the year of the search is used as the BAQ filter but can’t find a way to mix it into the “like” statement.

Here’s a statement in my BAQ that works by manually entering the year into the statement.

Sum(Case When LaborDtl.JobNum like ‘2020%’ then LaborDtl.LaborHrs Else 0 End)

The above statement functions as needed but I’d like to replace “2020” with the year extracted from a date parameter. Here’s code I’m using for that and it returns 2020 as an integer correctly when the start date is in the year 2020.

datepart(year, @StartDate)

Is there any way to mix the extracted year into the like statement above?

Thanks in advance.

2

You could cast the result of extracting the year from the date, to a nvarchar.

the following might work (emphasis on “might”)

SUM (Case WHEN LaborDtl.JobNum LIKE (convert(varchar, datepart(year, @StartDate))+'%') THEN LaborDtl.LaborHrs ELSE 0 END)

3

Getting this when checking the syntax.
“Invalid parameter 1 specified for datepart”.

4

I did test it and it does work for me (in 10.2.300.38).

@StartDate needs to be of the type Date`

(convert(varchar, datepart(year, @StartDate))+'%') does the following:

  1. Extracts the year from parameter @StartDate. this result is an integer
  2. Converts that integer to a string
  3. And finally, adds the % to the end of that string.
5

That’s exactly what I need to do but it’s not working for me in 10.2.600.5. I keep getting the “Invalid parameter 1” error mentioned earlier when checking the syntax using your statement. Googled that error and found mention of different versions of SQL needing different datepart syntax so tried changing Year to “y” or y (without quotes) and the syntax then checked as OK but it still doesn’t work to pull the parameter year into the like statement.

Crazy. Not sure what I’m missing. By the way, thanks for the help so far.

6

I’d debug it by first making a calc column of type Integer, with the formula to extract the year from the date.

And just asking again … is your @StartDate parameter of the type Date?

And I’m not sure if the parameter index (the “1” in “Invalid parameter 1 specified for datepart”.) is zero based (i.e. year is param 0, and @StartDate is param 1)

7

Yes. The startdate parameter is type date. Thanks again for your suggestions. I’ll have to try debugging more tomorrow, had to clock out for the day.
Dave

8

Calvin,
This morning I created a test BAQ and your statement did work fine there. It must have something to do with the statement being inside an inner sub-query because the same statement would not work there and kept giving the “parameter 1” error. Finally, got it to work by adding another calculated field, just to convert the year to nvarchar, before trying to use it in the like statement.

This extracted the “Year” from the parameter date:
datepart(year, @StartDate)

Added “CurrYear” calculated field to join the extracted date to the “%”:
(convert(varchar, Year)+’%’)

Then used “CurrYear” in the like statement:
Sum(Case When LaborDtl.JobNum like CurrYear then LaborDtl.LaborHrs Else 0 End)

Works great with only one added field. Not sure why it was needed, don’t really care… IT WORKS!!!

Thanks again for all your help, it’s appreciated.
Dave

1 Like