Date Parameter - use multiple formats in sql

This is my sql in a data set... 
 
SELECT CMNTYP, CMNNAM, SUM(HRSWRK)as WORK, SUM((HRSWRK)*7.25)AS DOLLAR FROM CMLIB.CMPF_F_CSD F 
INNER JOIN(select sum(amtset-amtsus) as csref, caspre, casnum 
from cmlib.cmlsntcs 
where dkttyp='CS' and 
herngdat between ? and ? and 
stscod not in(4,80,77) and 
concur=' ' and cmltyp in(' ', 'R') 
group by caspre, casnum 
order by caspre, casnum)A ON A.CASPRE=F.CASPRE AND A.CASNUM=F.CASNUM 
INNER JOIN CSFILE.CSPAGTMF G ON F.CSAGYNUM=G.CMNANO 
WHERE CSCSA='CS' AND HRSWRKDAT BETWEEN ? AND ? 
GROUP BY CMNTYP, CMNNAM 
ORDER BY CMNTYP, CMNNAM 
 
I have 2 parameters, a from and to date. The first line in red uses the format the parameters are entered. The 2nd line in red needs the dates to be changed from a simple 20100215 to a simple '2010-02-15'. I'm trying to do this in the parameters section/expression builder by breaking out the parameter using .toString. I get an error "cannot call method "toString" of null. 
 
Term1=params["iseriesfromdate"].value.toString(); 
date1=Term1.substr(0,4); 
date1b=Term1.substr(4,2); 
date1c=Term1.substr(6,2); 
"'"+date1+"-"+date1b+"-"+datec+"'"; 
 
Very new. Am I in the wrong area? Should I not be using toString? 
 
Tina

Find more posts tagged with

Comments

mwilliams

Hi Tina,

Changing the formatting of your date and putting it in your queryText in your beforeOpen script would be an option here. You could change your formatting using the SimpleDateFormat() function and store both formats of the dates and build your query with them. Let me know if you have questions.

tfield

Michael,

Thanks for your reply. I think I do need more direction, but I may have been misleading earlier when I said I have parameters of a from and to date. They look like dates, but are defined in BIRT as Float and are stored in both tables as numbers, not dates.

Thanks,

Tina

mwilliams

Tina,

In that case, you could just do a .toString() on your 20100215 value and then use the substring function to grab the first 4 characters, add the dash, add the second set of characters, add the second dash, and then the last of the string. Then you'd have your date string. If you needed it to be an actual date data type, you could use the SimpleDateFormat to parse the string into a date. Am I more on track to what you were thinking of doing now?

tfield

Not to confuse you, but I decided to have the parameters as a date and I will format the dates in the script. Here's what I did on the before open scipt...

var FromDate = new Date();
FromDate = params["iseriesfromdate"].toDate();

var iSeriesFrom = FromDate.getFullYear() + FromDate.GetMonth() + FromDate.GetDate();
var CSWorkFrom = FromDate.getFullYear() + "-" + FromDate.GetMonth() + "-" + FromDate.GetDate();

var ToDate = new Date();
ToDate = params["iseriesToDate"].toDate();

var iSeriesTo = ToDate.getFullYear() + ToDate.GetMonth() + ToDate.GetDate();
var CSWorkTo = ToDate.getFullYear() + "-" + ToDate.GetMonth() + "-" + ToDate.GetDate();

from here (if this is correct), I don't know what I need to do in the dataset sql to get the variables to be recognized. For example, in the dataset sql, I did this...

HRSWRKDAT BETWEEN CSWorkFrom AND CSWorkTo

but that is not working

More guidance wise one

Thanks,

Tina

mwilliams

For the iSeries variable values, you'll probably need to make the integers that you get from the getDatePart() functions into strings before adding/concatenating them. If not, you're getting:

2010 + 02 + 16, which is 2028, not 20100216.

As for using these values in your query, you'll just build your query in your script with something like:

this.queryText = "select blah from blah where Date between '" + fromDate + "' and '" + toDate + "'";

Hope this helps.

tfield

Michael, 
 
You've been a great help in working me through this. I have added the code to the script and it's working. I do have one last issue before I leave you alone. I've included my script again to show that the date that has the dashes is working fine, but at the moment, I still have the date that is just a number "hard coded". Could you help me through getting the iSeriesFrom and iSeriesTo dates to look like 20100701. 
 
var FromDate = new Date(); 
FromDate = params["iseriesfromdate"].value; 
 
//var iSeriesFrom = FromDate.getFullYear() & FromDate.GetMonth() & FromDate.GetDate(); 
var CSWorkFrom = FromDate; 
 
var ToDate = new Date(); 
ToDate = params["iseriesToDate"].value; 
 
//var iSeriesTo = ToDate.getFullYear() & ToDate.GetMonth() & ToDate.GetDate(); 
var CSWorkTo = ToDate; 
 
this.queryText="SELECT CMNTYP, CMNNAM, SUM(HRSWRK)as WORK, SUM((HRSWRK)*7.25)AS DOLLAR FROM CMLIB.CMPF_F_CSD F " + 
"INNER JOIN(select sum(amtset-amtsus) as csref, caspre, casnum " + 
"from cmlib.cmlsntcs " + 
"where dkttyp='CS' and " + 
"herngdat between 20100701 and 20101231 and " + 
"stscod not in(4,80,77) and " + 
"concur=' ' and cmltyp in(' ', 'R') " + 
"group by caspre, casnum " + 
"order by caspre, casnum)A ON A.CASPRE=F.CASPRE AND A.CASNUM=F.CASNUM " + 
"INNER JOIN CSFILE.CSPAGTMF G ON F.CSAGYNUM=G.CMNANO " + 
"WHERE CSCSA='CS' AND HRSWRKDAT BETWEEN '" + CSWorkFrom + "' AND '" + CSWorkTo + "'" + 
"GROUP BY CMNTYP, CMNNAM " + 
"ORDER BY CMNTYP, CMNNAM "; 
 
Thank you!!! 
 
Tina

mwilliams

Tina,

What is your parameter like when they enter it again? I thought it was like what you're wanting, i.e. 20100701. If not, what is the format of the parameter and what is the data type of the field you're comparing this to in the database? Is it a date, integerDate, or a string?

tfield

Parameter from: 2010-07-01
Parameter to: 2010-12-31

Both parameters defined as string

The database of the one I'm still having trouble with is using AS/400 tables, so the dates are simply numeric 8,0. So at this point I just need to figure out how to get parameter 2010-07-01 to 20100701 in the script.

Above where I said: var CSWorkFrom = FromDate;
It's actually: CSWorkFrom = params["FromDateParam"].value;

The same for To Date. Basically I'm doing nothing with FromDate=new Date();

mwilliams

If your parameters are of type string, you should just be able to run the replace() function on your parameter value to replace "-" with "". That should do it. Let me know.

tfield

You're awesome!! It's beautiful! Thanks Michael!!

var iSeriesTo = params["ToDateParam"].value.replace(/-/g, "");

mwilliams

Not a problem. Always glad to help. Let us know whenever you have questions!