I have a large text file (more than 300 million records). There is a field containing date in YYYYMM format. Target field is of date type and I'm using MS SQL 2008 R2 server. Due to huge amount of data I'd prefer to use bulk insert.
Here's what I've already done:
bulk insert Tabela_5
from 'c:\users\...\table5.csv'
with
(
rowterminator = '\n',
fieldterminator = ',',
tablock
)
select * from Tabela_5
201206
in file turns out to be 2020-12-06
on server, whereas I'd like it to be 2012-06-01
(I don't care about the day).
Is there a way to bulk insert date in such format to a field of date type?
kind regards
maciej pitucha
There isn't a way to do this on Bulk Insert. You have 2 options.
- Insert the date as into a varchar field then run a query to convert
the date into a DateTime field
- Use SSIS
Run SET DATEFORMAT ymd
before the bulk insert statement
Note that yyyy-mm-dd
and yyyy-dd-mm
are not safe in SQL Server generally: which causes this. Always use yyyymmdd
. For more, see best way to convert and validate a date string (the comments especially highlight misunderstandings here)
You may not care about the day but SQL does.
YYYYMM is not a valid SQL date.
A date must have day component.
In your example it parsed it down as YYMMDD.
You could insert into a VarChar as Jaimal proposed then append a 01 and convert.
I would read in the data in .NET add the 01 and use DateTime.ParseExact and insert row by row asynch. You can catch any parse that is not successful.
Or you might be able to do a global replace in the csv "," to "01,". It is worth a try.