UCCX Script - DBWRITE STEP NOT WORKING

[Jefferson Islan Silva]

Hi folks,

I'm trying to write a record on the SQL Server 2005, but I'm getting a trouble to date field as below:

INSERT INTO CC_PESQUISA_SATISFACAO (CALLING_NUMBER,CALLED_NUMBER,AGENT_ID,CALL_DATE,QUEUE_NAME,SCORE) VALUES ( 8092 , 562426 , 'jisilva' , Mon Mar 18 20:02:25 BRT 2013 , 'CSQ_CC_UNIMED_EXM_EXAMES' , 5 )

java.sql.SQLException: The name "Mon" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

SQL: INSERT INTO UNIMED_CC_PESQUISA_SATISFACAO (CALLING_NUMBER,CALLED_NUMBER,AGENT_ID,CALL_DATE,QUEUE_NAME,SCORE) VALUES ($INT_CALLING_NUMBER,$INT_CALLED_NUMBER,$STR_AGENT_ID,$DT_CallDate,$STR_QUEUE_NAME,$INT_SCORE)

How I can convert the date variable to a format that is accepted by MS SQL 2005 and UCCX?

Thank you

[Gergely Szabo]

Hi,

where is this date coming from - is it a String, a Date?

Or, actually, do you only need a timestamp?

If a timestamp (of the record appearing in the database table), use "GETDATE()" (w/o the quotation marks) instead of $DT_CallDate.

SQL Server generally accepts the yyyy-mm-dd HH:MM:SS format, so '2013-03-18 20:02:25' as a String should work, too.

Again, where is this date coming from?

G.

[Jefferson Islan Silva]

Date is coming from enterprise variable

[Gergely Szabo]

Alright, it's easy then, format the date to yyyy-mm-dd hh:mm:ss and enclose it with single quotation marks, MS SQL should accept it.

G.

[Jefferson Islan Silva]

How I can a set the string to this format?

[Gergely Szabo]

Can you give me an example of the value of the above mentioned enterprise variable?

[Jefferson Islan Silva]

No, I need to set the string and then put that to enterprise variable.

I need help to create this kind of string.

[Gergely Szabo]

I don't understand. You said this value is coming from an enterprise variable. I need an example of the value of this enterprise variable so I can tell you how to transform it into a string with the correct format.

Can you explain where you are getting that date from? From the very beginning.

[Jefferson Islan Silva]

Sorry, I'm setting the date via string D[NOW], but this is not getting the correct format to save it on database.

I need the string set working as SQL Server default.

[Gergely Szabo]

So it's actually the timestamp of the current date and time, right?

Then you don't need to format anything, just send GETDATE() and the SQL server will know that it needs to insert the actual date and time there.

g.

[Jefferson Islan Silva]

I need to get the timestamp, because I will manipulate that.

How I can get it?

[Gergely Szabo]

Exactly.

Or, use it like "" + D[now].year + "-" + D[now].month etc.

Check out the Expression Language Reference Guide for your UCCX version.

Or, if you are not afraid of Java, you can do this in a Set step with a String type variable, named, for instance, dateString:

Set dateString = {

java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

return sdf.format(new Date());

}

This would return something like 2013-03-21 18:20:01 which is acceptable by MS SQL.

G.