Javascript to capture the Date Prompt Value to a TextBox Prompt

[Kiran Kandavalli]

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.

Code Select Expand


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

[dougp]

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?

[Kiran Kandavalli]

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

[CognosPaul]

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.

[dougp]

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.

[Kiran Kandavalli]

Thank a lot both. its working as expected.

Many Thanks!
Kiran