topic Use a date context.value into an SQL statement in Talend Studio

Use a date context.value into an SQL statement

Sylcha — Fri, 15 Nov 2024 21:34:58 GMT

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 !

Re: Use a date context.value into an SQL statement

Serphentelm — Thu, 13 Jul 2023 13:43:05 GMT

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?

Re: Use a date context.value into an SQL statement

Sylcha — Thu, 13 Jul 2023 15:32:49 GMT

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....

Re: Use a date context.value into an SQL statement

Serphentelm — Fri, 14 Jul 2023 06:40:03 GMT

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