bulk insert in datetime field

Question

bulk insert in datetime field

mayank jarsaniya

Hall of Fame

Points: 3532
More actions
March 15, 2010 at 4:04 am

#137890

hi i have text file which contains date field in format like 'Mar 12 2010 1215PM,Jan 1 1900 1200AM'
i am using
create table #temp(id datetime)
bulk insert #temp
from '\\lserver\F\DBA\mynk_test\reviewsoft.txt' ;
select * from #temp
DROP TABLE #temp
but gives error 'Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (id).'

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

mjarsaniya (3/15/2010)
hi i have text file which contains date field in format like 'Mar 12 2010 1215PM,Jan 1 1900 1200AM'

That is not one of the many date/time formats supported by SQL Server.

The character format used in the input file must be implicitly convertible to a SQL Server date/time data type.

See CAST and CONVERT for available styles.

As it happens, the examples given would be OK, if there were a colon in the time format:

Mar 12 2010 12:15PM

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

mayank jarsaniya Hall of Fame Points: 3532 More actions · Answer 2

how can i use cast during balk insert from file option ....there are all available options are field terminator,row terminator.....

Paul White SSC Guru Points: 150468 More actions · Answer 3

mjarsaniya (3/15/2010)
how can i use cast during balk insert from file option ....there are all available options are field terminator,row terminator.....

You can't. Either import the data as a string and convert it afterward, or re-create your input file in a compatible format.

If the format is fixed, large, and regularly received, you might consider writing an SSIS task to process it.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi