CRS IVR script with SQL DB

Unanswered Question
Jul 6th, 2007

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.



I have this problem too.
0 votes
  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)

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;




Michael Gross Mon, 07/09/2007 - 11:51

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.




This Discussion