bulk insert datetime data

Question

Hi,

I try to bulk insert some datetime values in this format:

31/12/2005 00:00:00

using something like this:

create table Seed
(
    StartDate datetime not null
)
BULK INSERT Seed
FROM 'd:\dump\Seed.txt' 
WITH 
( 
    firstrow=2,
    FIELDTERMINATOR = '\t', 
    ROWTERMINATOR = '\n'
)

But I get this:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row

I know how to define a codepage but which? Is there a simple solution?

Thanks.

Christian

Answer 1

SQL Server is not going to convert the DD/MM/YYYY date format correctly. You'll need to either reformat your input file as MM/DD/YYYY or insert into a char/varchar datatype and then manipulate the string into the correct format for another datetime column. For example:

create table TempSeed
(
    StartDate varchar(50) not null
)

BULK INSERT TempSeed
FROM 'd:\dump\Seed.txt' 
WITH 
( 
    firstrow=2,
    FIELDTERMINATOR = '\t', 
    ROWTERMINATOR = '\n'
)

create table Seed
(
    StartDate datetime not null
)

insert into Seed
    (StartDate)
    select CAST(substring(ts.StartDate,4,3) + stuff(ts.StartDate,4,3,'') as datetime)
        from TempSeed ts

Answer 2

What is the default language for the user logged in to the SQL instance while running this t-sql? The date format you specified 31/12/2005 00:00:00 looks to be british and perhaps your default language is us_english.

Try running this t-sql to determine your current language:

SELECT @@language, @@langid

If it's us_english, then your date format should be mm/dd/yyyy hh:mm:ss

To keep your example alive, try changing your default language for the current user by doing the following:

--Get the current language setting for connected user
SELECT @@LANGUAGE,@@LANGID

--Get information about all languages
EXEC sys.sp_helplanguage

--Get the name of the current user
DECLARE @sysuser NVARCHAR(30)

SET @sysuser = SYSTEM_USER

PRINT @sysuser
EXEC sp_defaultlanguage @sysuser, 'british' --satisfying your example date

After you have changed the default language, reconnect your query window and you should be now utilizing the new default language.

To get back to the previous language setting, just EXEC sp_defaultlanguage again with the language setting you previously had.

Hopefully that works!