CSV to SQL: DateTime Seconds Missing in BULK INSERT

Data and Databases
1

I’m having a weird issue with BULK INSERT in MSSQL. I’m importing data from CSV files made by our machines. The data goes in fine, but there’s a problem with the datetime column.

The dates and times (hours and minutes) are correct, but the seconds are always zero. This happens for all records in every table I’ve used BULK INSERT on.

Here’s what my data looks like in the CSV:

Widget123,Widget123,2017-03-10 06:01:16,,,,Machine_A,1,0,OK,NA,NA,NA,0

And here’s my BULK INSERT command:

BULK INSERT MyDB.dbo.ProductData 
FROM '\fileserver\CSVs\datafile.csv'  
WITH (
  FIELDTERMINATOR = ',', 
  FIRSTROW = 2, 
  MAXERRORS = 0, 
  ROWTERMINATOR = '\n'
)

But when I check the table, I see this:

ProductType | SerialNum | TestDate | ... | Status
Widget123   | Widget123 | 2017-03-10 06:01:00.000 | ... | OK

The datetime column is set as DATETIME. Any ideas why the seconds are disappearing?

2

I’ve encountered this issue before, and it’s often related to the default style BULK INSERT uses for datetime parsing. One solution that worked for me was explicitly specifying the dateformat in the BULK INSERT statement. Try adding ‘DATEFORMAT = “yyyy-mm-dd hh:mm:ss”’ to your WITH clause. This forces SQL Server to recognize and import the full datetime, including seconds. If that doesn’t resolve it, you might need to look into using a format file as Sam suggested, or consider alternatives like the bcp utility or SSIS for more granular control over data import.

3

hmmm, interesting problem! Have u tried converting the datetime column to a string before importing? Like:

ALTER TABLE MyDB.dbo.ProductData ALTER COLUMN TestDate VARCHAR(30)

then after BULK INSERT, convert back to DATETIME? might preserve those pesky seconds. just a thought!

4

hey man, bulk insert sometimes loses seconds. try OPENROWSET with a format file, it handles datetime properly. something like:

SELECT * INTO MyDB.dbo.ProductData
FROM OPENROWSET(BULK ‘\fileserver\CSVs\datafile.csv’,
FORMATFILE=‘C:\path\to\format\file.fmt’) AS a;

should work. good luck!