CODEWITHSUNDEEP
sqlsql-serverdatedatetimebulkinsert
Mahendran V M
Mahendran V M

Reputation: 3496

How to get datetime value from data?

I have one csv file that contains one column with following data,

31/10/2014 10:27:12-05
31/10/2014 10:27:13.123-05
31/10/2014 10:27:14.123456-05

I need to move that data into Table named "CsvData" which contains one column data1 which is datetime datatype.

Generally datetime format is should be like this "YYYY-MM-DD hh:mm:ss[.nnn]", but my data is looks different. I have over 100k rows like above 3 lines structure.

My Sample Output look should like below:

31/10/2014 10:27:12
31/10/2014 10:27:13
31/10/2014 10:27:14

I'm looking for good way to process entire file into SQL Server.

How to proceed?

Upvotes: 0

Views: 573

Answers (5)

sepupic
sepupic

Reputation: 8687

Import your file using format file where define this field as varchar(100) instead of datetime, after importing it as string take only 19 characters and cast them to datetime (selecting in a new table or adding a new datetime field)

Update: you can ask bcp to generate format file, like this:

    create table dbo.dt_str(col1 varchar(100)); -- the table from which to generate fmt-file
    exec xp_cmdshell 'bcp b.dbo.dt_str format nul -c -t, -f z:\temp\dt_str.fmt -T'; --can be launched from cmd without xp_cmdshell

This generated the following file (3 rows):

10.0

1

1 SQLCHAR 0 100 "\r\n" 1 col1 Latin1_General_CI_AS

Then you load your file like this:

    BULK INSERT dbo.dt_str
    FROM 'z:\temp\dt.csv'
    WITH (FORMATFILE = 'z:\temp\dt_str.fmt');

Control what is imported and if it can be converted to datetime:

    select col1, convert(datetime, left(col1, 19), 103)
    from dbo.dt_str;

Select into new_table:

    select convert(datetime, left(col1, 19), 103) as dt
    into dbo.dt_new
    from dbo.dt_str;

More on format files here: Use a Format File to Bulk Import Data (SQL Server)

Upvotes: 1

neer
neer

Reputation: 4082

You can use LEFT when importing.

DECLARE @Val NVARCHAR(100) = '31/10/2014 10:27:14.123456-05'
SELECT LEFT(@Val, CHARINDEX('-', @Val) - 1)

Upvotes: 1

Prabhat G
Prabhat G

Reputation: 3029

You need to create a staging table and insert your records from csv as it is. Then try SELECT LEFT('31/10/2014 10:27:14.123456-05',19) to transform and load from staging table to main table.

Upvotes: 1

Ven
Ven

Reputation: 2014

This will help.

select convert(varchar(19),'31/10/2014 10:27:12',120) -- for datetime

select convert(varchar(10), '31/10/2014 10:27:12',120) -- for date only

Upvotes: 1

Mansoor
Mansoor

Reputation: 4192

Use LEFT of built in string function : 

SELECT LEFT('31/10/2014 10:27:12-05',19)

OR 

SELECT LEFT(Your_columnname,19)
FROM your_table

Upvotes: 1

Related Questions