TalendDate in MssqlInput

[Anonymous]

Hi

I am trying to pass a variable in a MSSqlInput component to retrieve the current UTC time. 

I am using the talend function TalendDate.formatDateInUTC("yyyy-MM-dd HH:mm:ss", TalendDate.getCurrentDate())

However when i place this into the MSSQL component and attempt to declare it as a variable i am getting some error. I have tried many variations, but still can not get it to work. 

tMSSqlInput

Query:

"
DECLARE @DtNow DATETIME = TalendDate.formatDateInUTC(\"yyyy-MM-dd HH:mm:ss\", TalendDate.getCurrentDate() )
"

I am getting the below error

[statistics] connected
Exception in component tMSSqlInput_2 (Load_StatusCredits_CC)
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find either column "TalendDate" or the user-defined function or aggregate "TalendDate.formatDateInUTC", or the name is ambiguous.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:677)
[FATAL]: wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC - tMSSqlInput_2 Cannot find either column "TalendDate" or the user-defined function or aggregate "TalendDate.formatDateInUTC", or the name is ambiguous.
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find either column "TalendDate" or the user-defined function or aggregate "TalendDate.formatDateInUTC", or the name is ambiguous.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC.tMSSqlInput_2Process(Load_StatusCredits_CC.java:916)
at wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC.runJobInTOS(Load_StatusCredits_CC.java:1614)
at wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC.main(Load_StatusCredits_CC.java:1248)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:677)
at wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC.tMSSqlInput_2Process(Load_StatusCredits_CC.java:916)
at wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC.runJobInTOS(Load_StatusCredits_CC.java:1614)
at wombat.load_statuscredits_cc_0_1.Load_StatusCredits_CC.main(Load_StatusCredits_CC.java:1248)
[statistics] disconnected

[Anonymous]

Hi,

     Since you are using tMSSQLInput component, you will have to write a SQL query to extract the UTC time. Could you please try the below query?

SELECT GETUTCDATE() 

     https://docs.microsoft.com/en-us/sql/t-sql/functions/getutcdate-transact-sql?view=sql-server-2017

     You are currently trying to run the declare part in MSSQLInput component. If your intention is to run a stored proc, could you please try tMSSQLSP component?

      If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

[cterenzi]

Everything inside the quotes in your query is sent to the database as-is.  What you want is to drop in the current timestamp at runtime.  To do this, alter your query like this:

"
DECLARE @DtNow DATETIME = '" + TalendDate.formatDateInUTC("yyyy-MM-dd HH:mm:ss", TalendDate.getCurrentDate() ) + 
"'

SELECT @DtNow;"

When the job runs, the current date will be added to the query string and passed to the database.  

[Anonymous · Accepted solution]

Thanks guys,

I was able to get it running using the below code;

"

DECLARE  @DtNow DATETIME =  '"+TalendDate.formatDateInUTC("yyyy-MM-dd HH:mm:ss", TalendDate.getCurrentDate())+ "'

"