Update Date value in SQL

[YFA]

Dear All,

I have a SQL table. It is including two date input column and their types smalldatetime.

I need update their value via executequery command. I can updating nvarcar and int values via below command set.

UPDATE table_name SET column_name = new_value WHERE column_name = old_value

But it is not work for date updating.

I hope any one may help to me.

[anson.kunjappy]

Hello,

To gain a better understanding of your problems, could you post images of your code here?

Anson K
Applications Engineering

National Instruments

www.ni.com/support

[YFA]

Dear Anson,

You may find details of my project in attachments.

dbtable.png shows design of table, tablerow.png shows a row from table and query.png shows update.vi.
I can update varcar and int types datas via update.vi but It is not working for datetime types inputs.

When I try update datetime types update I get "conversion failed when converting date and/or time from character string" error.

Same problem applies at filtering operation between selected datetimes. 

[pincpanter]

How do you format the date string? First, you should use DB Tools Format Datetime String.vi.

However, depending on the database type and the regional date format settings, you may found some crazy behaviour. You may need to swap month and day in the string.

I suggest you to temporarily create a table with a single datetime column, add one record for each day in the year, then verify the correctness for each one.

[YFA · Accepted solution]

Dear All,

I fixed my problem thanks to change datetime column type to varchar.

I used Format Date/Time String function for add input to datetime column.

I can update and filtering database via execute query tools now.