Use a date context.value into an SQL statement

[Sylcha]

Hi Community,

hi set an context value like context.LastUpdate = TalendDate.getCurrentDate();

Then i have an SQL (MSSQL SERVER) statement which try to ubdate a column with

context.LastUpdate like :

UPDATE My_Table SET UpdateInProgress =0,LastUpdate="+context.LastUpdate+"

If i print my context.LastUpdate got

LastUpdate = "Thu Jul 13 15:16:29 CEST 2023" when i run this job, got error like

"Exception in component tMSSqlRow_1 (JOB_My_Job)

java.sql.SQLException: incorrect Syntaxe near 'Jul'."

does anyone know how can i deal with this ?

thank you all !

[Serphentelm]

hi @Sylvain CHARRONDIERE​ , that's more an sql problem than a compile error.

Probably the format of the date is not fitting the metadata of your table in sql?

Likely, date should be withing quotes, as follow: update my_table set my_date = '2023-07-13'

Have you tried running directly that query via Sql management studio or the client you're using?

[Sylcha]

Hi,

if i try to do a

UPDATE My_Table

      SET   UpdateInProgress = 0, LastUpdate = 'Thu Jul 13 15:16:29 CEST 2023'

in a Sql management, that did not work : Échec de la conversion de la date et/ou de l'heure à partir d'une chaîne de caractères.

I may should transform this date string but no idea how....

[Serphentelm]

Date management is always troublesome.

I suggest to try using the talend routine getDate instead getCurrentDate. This way you can choose the format output that you desire. Since you want to write it into an SQL Server table, I recommend using this format: yyyy-MM-dd

Regards