How Use Bulk Insert Csv To Sql Server With Datetime Format Correct?

i want use bulk insert file csv insert to SQL Server 2012. same column have datetime but use bulk insert datetime format not work and i not use SSIS. Example Create Table CREATE T

Solution 1:

You need to change the DATEFORMAT to DMY. Adding the following to the top of your script should work:

SET DATEFORMAT DMY;

So your full script should be:

SET DATEFORMAT DMY;

declare@pathvarchar(255),
    @sqlvarchar(5000)           

SET@path='C:\Test\TESTFILE.csv'set@sql='BULK INSERT [dbo].[scanindex_test] FROM '''+@path+''' 
      '+'     WITH (      
                CODEPAGE=''RAW'',           
                FIELDTERMINATOR = '','', 
                ROWTERMINATOR = ''\n''
                ) '
print @sqlexec (@sql)

Solution 2:

It works with Bulk Insert:

SET DATEFORMAT DMY
BULK INSERT [dbo].[TRX]
        FROM'C:\..\trx2.txt'WITH
    (           
                FIELDTERMINATOR ='\t',
                ROWTERMINATOR ='\n',
                FIRSTROW =2
    )
PRINT 'BULK INSERT [TRX2]'
GO