Getting problem while using getDate() to insert date in the Bulk insert

Question

Post reply

Getting problem while using getDate() to insert date in the Bulk insert

Munish Gupta

SSC Veteran

Points: 278
More actions
December 19, 2005 at 11:21 pm

#364482

Iam getting error like
Bulk insert data conversion error (type mismatch)
While inserting a date with getdate() in the file specified for the bulkinsert to insert a currentdate in the datetime col in the table.

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

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

Papillon Hall of Fame Points: 3303 More actions · Answer 1

Can u specify that bulk insert query n both table structure...

Regards,

Papillon

Munish Gupta SSC Veteran Points: 278 More actions · Answer 2

CREATE TABLE table1 (

Field1 varchar(255),

Field2 datetime

)

and bulk insert query

BULK INSERT table1 FROM '<path>\xyz.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '\t')

and content of xyz.sql

aa<Tab>getDate()

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That would be because SQL's trying to insert the string value 'getDate()' into a datetime field. Bulk insert just puts data into a table. It won't check to see if it's actually a function specified in the incoming data.

To do what you're trying, add a default of GetDate() of Field2 in table table1 and don't specify that field in your bulk insert (you may need a format file for that).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLBill SSC Guru Points: 51440 More actions · Answer 4

How about something like:

CREATE TABLE table1 (

Field1 varchar(255),

Field2 datetime DEFAULT (GETDATE())

)

remove the GETDATE() from your xyz.txt file and run the bulk insert query

BULK INSERT table1 FROM '\xyz.txt' WITH (ROWTERMINATOR = '\n')

-SQLBill