Team,
Do anyone have the code to capture the Date Prompt Parameter value to an integer in TextBox Prompt. Please provide.
I found this code in Google but not sure how to use this.
The Reason I am using JavaScript is we have a Database "Denodo" that is not having to_Date and to_char functions same like we use in Oracle / some other Databases. Also I am connecting Denodo using Postgress Driver which all functions are not working through Cognos.
var form = getFormWarpRequest();
var textB = form._textEditBoxMyStartDate;
var temp = form.txtDatemystartdate;
var myDate = new Date(temp.value);
var myDay = myDate.getDate();
var myMonth = myDate.getMonth() + 1;
if (myMonth < 10 )
{
myMonth = "0"+myMonth;
}
if(myDay < 10)
{
myDay = "0" + myDay;
}
var myDateInt = myDate.getFullYear() + myMonth +myDay;
textB.value =myDateInt ;
promptButtonFinish();
Thanks!
Kiran
I hate it when people do this to me, but...
Why? What is the end game? How about using the date and having a Cognos macro perform the manipulation?
Hi,
Thanks for your response.
1) Initially the Date Key is in Date Format (YYYYMMDD)
2) Now the Database team have converted it to String (2017-01-01) in the Fact Table.
Based on the Date Prompt Selection the Fact Table Records have to be filtered. Can you please suggest me how to implement using macro.
Thanks!
Kiran
I think the initial value is ideal. With that you could very easily filter on ranges. Strings don't give you that ability.
Macro functions do give you the ability to convert a date to a number like that.
Easiest way:
#sq(
timestampMask(prompt('Date','date')+'T12:00:00Z','yyyymmdd')
)
#
This would convert a date like 2017-11-20 to 20171120. It adds the time to the date to make it a valid date, and uses the timestampMask to make the conversion. The SQ function simply wraps the value in single quotes. 20171120 to '20171120'.
Another way might be something like:
#
join('',
split('-',
prompt('Date','date')
)
)
#
This takes the date 2017-11-20, converts it into an array splitting on the dash, [2017,11,20] and joins it with no delimiter, 20171120.
Wow, Paul! Quick response. Maybe we both need more work to do this morning. :P
I agree. I prefer the int version (YYYYMMDD), but we don't know why the database team changed it to a string. If it can't be changed back to an int...
#sq(
timestampMask(prompt('Date','date')+'T12:00:00Z','yyyy-mm-dd')
)
#
...will turn a date like 2017-11-20 into a string like '2017-11-20'.
And, technically, Paul's code also returns a string like '20171120'. To return an int, leave out the sq() function:
#
timestampMask(prompt('Date','date')+'T12:00:00Z','yyyymmdd')
#
Although most RDBMSs will implicitly case the string to an int.
Thank a lot both. its working as expected.
Many Thanks!
Kiran