excel 2010 get date in requested format

[jassie]

In excel 2010, I am placing the following syntax in each row of the excel spreadsheet.
= "Update trans set c_date = 'DATEVALUE("&P1&")' where trans_ID = '"&D1&"'"
I want to obtain the trans_id and c_date values from each applicable column. I am obtaining the
trans_id value with no problem. However I am having a problem obtaining the C_date value in the
format I need it to be. I would like the date to look like the following:
2013-01-29 17:04:00.207. This format is yyyy-mm-dd hh:mm:ss:000. The date can be in a short date format.
I originally obtained the value in the C_date column from a sql server 2008 database.
Thus can you tell me what I need to do obtain the C_date value in the format I need?

 

[Norie]

You could try using TEXT.

TEXT(P1, "yyyy-mm-dd hh:mm:ss:000")

 

[jassie]

You could try using TEXT.

TEXT(P1, "yyyy-mm-dd hh:mm:ss:000")

I am getting a synxtx error with the following line:
= "Update trans set complete_date = 'TEXT("&P1&,, "yyyy-mm-dd hh:mm:ss:000"")' where trans_ID = '"&D1&"'"

Can you tell me what is wrrong? I am new to excel and I do not know how to setup marcros like this.

 

[Norie]

Replace P1 in your original formula with what I posted.

Oh, and lose the 000.

= "Update trans set c_date = 'DATEVALUE("& TEXT(P1, "yyyy-mm-dd hh:mm:ss") &")' where trans_ID = '"&D1&"'"