BCP fails to load datetime fields in ISO8601 (yyyy-mm-ddThh:mi:ss) format - by Arnoud Berendsen

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 770899 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 11/13/2012 8:06:28 AM
Access Restriction Public

Description

BCP fails to load datetime information in ISO8601 (yyyy-mm-ddThh:mi:ss) format. 

Using a BULK INSERT statement, the same file can be loaded successfully. Simple INSERT INTO statements are also able to deal with this format successfully.
Sign in to post a comment.
Posted by Microsoft on 7/17/2013 at 12:53 PM
ODBC does not support this format. See http://msdn.microsoft.com/en-us/library/bb677233.aspx
If you feel that we should support this please let us know and we will take it into consideration in future repleases.
Posted by Microsoft on 7/12/2013 at 10:18 AM
ODBC does not support this format. See http://msdn.microsoft.com/en-us/library/bb677233.aspx

This is also reflected in the following comment inside ConvertToDateTime:


{ // Possible DATE/TIME/TIMESTAMP in ODBC char format
                // YYYY-MM-DD                         DATE
                // HH:MM:SS                            TIME
                // HH:MM:SS[.F...]                     TIME2
                // YYYY-MM-DD HH:MM:SS[.F...]         TIMESTAMP
                // YYYY-MM-DD HH:MM:SS[.F...][+HH:MM] TIMESTAMPOFFSET


The change to allow 'T' as well as a space should not be difficult, however this format has never been supported by ODBC.

If you feel that we should support this please let us know and we will take it into consideration in future repleases.
Posted by Microsoft on 5/3/2013 at 11:55 AM
I have reopened the issue and our dev is taking another look.
Posted by Arnoud Berendsen on 5/2/2013 at 4:01 PM
How are you not able to reproduce this issue? I'm having this issue still after 7 months, on every machine I have, and had to rewrite my loader not to use BCP anymore but instead generate sql statements. The steps to reproduce in my original report seem pretty clear. If I simply follow them exactly, the issue clearly appears.

Note that ISO8601 format with a space as separator between the date and hour part ('yyyy-mm-dd hh:mm:ss') works as expected, but when using the 'T' as separator, it fails ('yyyy-mm-ddThh:mm:ss') .

Please contact me directly by email if you still can't reproduce this.
Posted by Microsoft on 4/15/2013 at 4:27 PM
Thanks for your feedback. Unfortunately we are not able to reproduce the issue.