0

I insert data from CSV into SQL Server.

The table Tbl_art_tmp is:

Art nchvar(50)  
Qty nvarchar(50)
importedDate nvarchar(50)

SQL query to insert

BULK INSERT Tbl_Art_tmp 
FROM 'ExportArt.CSV' 
WITH 
    (FIRSTROW = 1,  
     FIELDTERMINATOR = ';', 
     ROWTERMINATOR='0x0a');

The csv, exported from another software, contained

Art;Qty;importedDate
10203R04-04; 0;2015-10-21
10407T-10-100; 9;2016-02-01

I want to update another table Tbl_art, the table:

art nvarchar(30)
Qty real
lastUpdate datetime

My SQL query:

UPDATE a
SET a.[qty] = CAST(atemp.[Qty] AS REAL),
    a.[lastUpdate] = CAST(atemp.[importedDate] AS DATETIME)
FROM [Tbl_Art] a
JOIN [Tbl_art_tmp] atemp ON a.[art] = atemp.[Art];

The update for Qty is ok not the datetime conversion

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

Thank you for your help

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tkw
  • 3
  • 1
  • 2
  • If you're only interested in the **date** (and no time portion), I'd recommend using `DATE` as your datatype in `tbl_Art` (not `datetime`). Using `DATE` should fix this error, too – marc_s Apr 15 '16 at 08:33
  • What does the `importedDate` field *really* contain? Are there any trailing characters, garbage etc? BTW you could have used the `date` or `datetime` for the staging table itself. As long as the date is in an unambiguous format like the unseparated `YYYYMMDD` or ISO 8601, the conversion will be made automatically. Otherwise, the column's collation will apply. Even that can be changed with a format file – Panagiotis Kanavos Apr 15 '16 at 08:33
  • 1
    Are you sure this isn't caused by dirty data? – Panagiotis Kanavos Apr 15 '16 at 08:36
  • i can't change it to date because later the client will use time – tkw Apr 15 '16 at 15:20

3 Answers3

0

Try with CONVERT(DATETIME,atemp.[importedDate ],102)

btw: Is there really a space behind the columnName "importedDate"?

The problem you are running into is probably culture / langugage related.

You might read this https://stackoverflow.com/a/34275965/5089204

Convert allows you to specify a dedicated format. The cast will just try to use your defaults

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • `cast('2016-02-05' as date)` or `cast('2016-02-05' as datetime)` should work without specifying a format. CAST can recognize ISO 8601 or the unseparated format. – Panagiotis Kanavos Apr 15 '16 at 08:36
  • @PanagiotisKanavos, you are right... Thx for pointing this out... I just checked it with the "full" ISO 8601 (with "T00:11:30"). This is recognized correctly, even without the "T". Never finishing to learn... – Shnugo Apr 15 '16 at 08:45
0

As Marc_s already answered here

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

  • YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

  • YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

So in your concrete case - use these strings:

insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00');

and you should be fine (note: you need to use the international 24-hour format rather than 12-hour AM/PM format for this).

Alternatively: if you're on SQL Server 2008 or newer, you could also use the DATETIME2 datatype (instead of plain DATETIME) and your current INSERT would just work without any problems! :-) DATETIME2 is a lot better and a lot less picky on conversions - and it's the recommend date/time data types for SQL Server 2008 or newer anyway.

SELECT
   CAST('02-21-2012 6:10:00 PM' AS DATETIME2),     -- works just fine
   CAST('01-01-2012 12:00:00 AM' AS DATETIME2)   -- works just fine

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

Community
  • 1
  • 1
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
  • As far as I understood, the OP is importing a CSV file and therefore has no choice than to take the date as it comes from there... – Shnugo Apr 15 '16 at 08:38
  • I change the type of lastUpdate to datetime2 and still not working this queries give me the same errors: 
    
SELECT CAST(importedDate AS DATE) FROM Tbl_Art_tmp; 
SELECT CAST(importedDate AS DATETIME) FROM Tbl_Art_tmp; 
SELECT CAST(importedDate AS DATETIME2) FROM Tbl_Art_tmp;
    still Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string.     – tkw Apr 15 '16 at 15:29
  • i am using sql server 2008 – tkw Apr 15 '16 at 15:29
0

I seems to be an error with the csv file... it's contain a space after the date i use SSIS package to schedule the import and he took care of the conversion without any problem thank you

tkw
  • 3
  • 1
  • 2
  • Hi, there's one more idea coming to my mind. In my answer I pointed to the additional space already ... Are you aware of the fact, thar windows standard line break takes two characters (13 and 11, which is CR and LF)? You are specifying the LF only. Might be, that the "space" is a CR actually ... – Shnugo Apr 16 '16 at 07:52