Hi All,

I've a text file which having a datetime column value like YYYY-MM-DD-HH.MM.SS.XXXXXX. I cannot convert this is to datetime format from text file using BCP utility. 

Presence of hypen "-" between DD and HH, the SQL server does not accept this is as Datetime.

Is there any option to covert the date value in format file in BCP.

Thanks in Advance....!

Regards

Baskaran R

• Edited by bpbhaskar 20 hours 32 minutes ago

Hi Bhaskar,

 I think that by default, it only accepts YYYY-MM-DD and YYYYMMDD. It may work if you use the -R switch to BCP. This switch enforces interpretation according to the system locale (which may be different from your own regional settings).

When you test, make sure that you test with a date like 11/09/2015, so that you can see that the dates are interpreted the way you want to interpret them.

I don't think bcp can do this conversion while loading. In that event, you create ssis data flow task and use derived column transformation to cast it to datetime after replacing the "-" between DD and HH.

BCP cannot handle converting that format. You will need to BCP the field into a character field and do the conversion yourself, after BCP is complete.