Updating entry into sql usijg stored procedure throwing error

[harish17]

Hi all,

I got data from SQL database as a collection, 3 of the fields are datetime(date time format) I am trying to add or update this field with new entries instead of updating this fields in one go I am doing each field to update individually when I try to update first one it is throwing an error some thing like this:
Incorrect syntax near ‘05’
Probably it's because I entered the date in one field ats11/17/2018 05:20:00 and other date field(11/17/2018 07:20:39) the error is like this:
Incorrect syntax near 07

We added manually to the date fields in collection current value it is throwing error as incorrect syntax 5 and 7 on respective fields so ,I thought it's because of format date so I used format date and make the time as HH:MM:SS as and as I said it throwed above error like incorrect syntax near 05 and 07 I. Respective fields

 

[sivagelli]

FormatDate() is limited to manipulating the format of the date. Example: Changing the format from 11/12/2018 to 12 Nov 2018
If you have Time as well, use FormatDateTime().

Please add screenshots for better understanding of your problem.

 

[harish17]

Hi Siva ,
Am sorry it was typo Mistake i was already Using FormatDateTime(ToDateTime([dt]),[format])
Here am Using
Data Sql Server(VBO) and action is Execute
I/p(Query) -"Update Basic.dateTable set Fulltime ="&[ft]&" , "&"DateT="& FormatDateTime(ToDateTime([dt]),[format])&" Where Name="&[nm]&""

Data item Type(dt) type - DateTime -initial Value -11/20/2018 9:20:30 AM
Data item Type(format) type- Text -intial Value - MM/dd/yyyy h:mm:ss tt
[ft]-Y
[nm]-Ram
When i Execute the above sql Query it is saying Incorrect Syntax near 9
and i tried by changing the Above "format" data item as - MM/dd/yyyy hh:mm:ss tt
Now it is saying Incorrect Syntax near 09
Not Sure why it is throwing That Error?

 

[VJR]

Didn't get a full picture of the issue but Step through the process and before running the Execute action capture the full SQL string (including the dates) and see how the query is getting submitted with what values and identify anything questionable for example a missing single quote in the parameters, etc.
If you are able to then also post your full query here.

 

[sivagelli]

Couple of questions...

1. Are you matching the format of column in table?
2. Is the exception from Blue Prism or SQL

If possible, post some screenshots.