0

I create a temp table 

CREATE TABLE #Test(theDate DATETIME)

I use this query to insert data into the temp table

INSERT INTO #Test VALUES(CONVERT(NVARCHAR, '2016-09-30' 21))

It works on one database (SQL Server 2005), but gives error on another (SQL Server 2008). I can't remember the exact error, but it has something to do with 'Error converting NVARCHAR TO DATETIME'

Why is working on one database, but not another? Is there a special property to enforce error on mismatched type? I can't find information anywhere.

Eric
  • 3,165
  • 1
  • 19
  • 25
  • 3
    Have you checked from language and collation on both servers and logins? – Horaciux Oct 01 '16 at 01:14
  • I don't have access to the other server anymore, but the other server is in Europe so most likely they will have different collation. – Eric Oct 03 '16 at 15:06

2 Answers2

3

There are several problems:

  • You use CONVERT to convert a string to a string while you want a DATE
  • You use NVARCHAR without a length (which is 1 by default) Bad habit to kick
  • There is a comma missing after the date literal
  • You are using 21 which is the format with a 2-digit-year Details here

Better was this

SELECT CONVERT(DATETIME, '2016-09-30', 121)

But even better was to avoid conversions at all.

In your case use Details here

  • unseparated: INSERT INTO #Test VALUES('20160930')
  • ODBC (my favorite): INSERT INTO #Test VALUES({d'2016-09-30'})

UPDATE

I cannot check this, no such versions installed, but I'm quite sure, that different default culture / language and implicit error correction leads to this behaviour...

On my SQL Server 2012 instance all of them work fine. The obvious format error (21 instead of 121) is corrected implicitly. The second obvious error (conversion to a 1-char-string) is corrected as well. Lower versions deal with this differently (probably).

If possible, try these lines on your servers...

SET LANGUAGE US_ENGLISH; --MDY
SELECT CONVERT(NVARCHAR, '2016-09-30', 21)

SET LANGUAGE GERMAN; --DMY
SELECT CONVERT(NVARCHAR, '2016-09-30', 21)

SET LANGUAGE JAPANESE; --YMD
SELECT CONVERT(NVARCHAR, '2016-09-30', 21)

The literal date format YYYY-MM-DD, even as this is short ISO8601, is not sure in all situations. That's why one should use culture independant formats always...

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • The missing comma is a translation problem. It still doesn't answer my question, why does it work on one database, but not on another? – Eric Oct 03 '16 at 15:10
  • The query above works on SQL Server 2005, 2008, 2012. I can't test it on the other server since I don't have access anymore. – Eric Oct 04 '16 at 16:23
0

You can try this if it is a problem related to language

declare @language as varchar(100)
SELECT @language= @@LANGUAGE 

SET LANGUAGE us_english 

--... your code...

SET LANGUAGE @language
Horaciux
  • 6,322
  • 2
  • 22
  • 41