Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Attention: The Cisco Support Community site will be in read only mode on Dec14, 2017 from 12:01am PST to 11:30am for standard maintenance. Sorry for the inconvenience.

New Member

CRS IVR script with SQL DB

I am building an IVR script that connects to an SQL database. I can't seem to figure out what variable type matches the data type java.sql.Timestamp and because of this I am pulling the date from the table as a string (java.lang.String).

Although I can pull this date in as a string, my problem is that I can't seem to figure out how to convert this string (such as "2007-06-02 05:00:48.000") to a date I can use. Ultimately I want to use the Create Generated Prompt to read the date. I'd be happy even stripping the string down to multiple variables (year, month, date, etc.). It seems like there must be a way of doing it with the expression editor, but I have not been able to figure it out yet.

Any help is much appreciated.

Thanks,

Mike

2 REPLIES
Green

Re: CRS IVR script with SQL DB

When Java requests the info from SQL, you use the CONVERT function in SQL - just as you have to get back a string.

Now use something like the following code. This assumes you have the date in a string variable "dueDateString" in the form "06-02-2007", so you'll have to move the substring indices around to suit your different ordering.

Declare a variable of type Date and use a SET node to set the value. Open up the Expression editor and add code like this.

{

// Create Integer types from substrings extracted from dueDateString

java.lang.Integer dayInt = new java.lang.Integer(dueDateString.substring(2,4));

java.lang.Integer monthInt = new java.lang.Integer(dueDateString.substring(0,2));

java.lang.Integer yearInt = new java.lang.Integer(dueDateString.substring(4,8));

// Create Date type with yy, mm, dd

// Have to subtract 1900 from the year, and 1 from the month (0-11)

java.util.Date d = new Date((yearInt.intValue() - 1900), (monthInt.intValue() - 1), dayInt.intValue());

// Return the new date

return d;

}

Regards,

Geoff

New Member

Re: CRS IVR script with SQL DB

Thank you very much for your response. This bit of code worked perfectly after making the substring index modifications you said I would need to make.

Thanks,

Mike

199
Views
0
Helpful
2
Replies
CreatePlease to create content