Toad World® Forums

Importing DATETIME Data


#1

Hi all,

We have some data to import, and one column (a tab delimited CSV file) is being imported into a DATETIME column, however it says it cannot import the data - as it ‘cannot be convert to type DATETIME’

It fails on the first row, the example value is;

‘15/06/2013 00:00:00’

It’s importing into SQL Server 2012.
I can get it to import by either changing the value to ‘2013/06/15’, or ‘15 june 2013’.

My main problem is, why won’t this import on TOAD 3.5 or 3.6, but will import on 3.1?

Is it that TOAD will not longer convert the value?
Or do I need to change a setting for it to be able to?

Thanks in advance.


#2

I asm surprised it converted that date format in any version. SQL Server expects dates to be mm/dd/yyyy or yyyy-mm-dd or some other formats but dd/mm/yyyy would get converted incorrectly unless the default date format on the server was changed, like for foreign systems (we in the US are the only ones who use mm/dd/yyyy). Basically 15 is an invalid month. You need to either output the data in a date format that can be implicitly converted or import the data as text and convert with a function.


#3

Currently we don’t support importing datetime values with dd/mm/yyyy format. I’ve created QAT-3994 for it.