cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
443
Views
0
Helpful
2
Replies

CRS IVR script with SQL DB

Michael Gross
Level 1
Level 1

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 2

geoff
Level 10
Level 10

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

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

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: