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.
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.