CODEWITHSUNDEEP
sql-serverasp-classiclocalization
Ian Warburton
Ian Warburton

Reputation: 15676

SQL Server expecting different date format

I have set the SQL Server's default language to "British". The following correctly returns 23...

sp_configure 'default language'

I have also set the language of my login to "British" too with...

 sp_defaultlanguage @loginame = 'Login123', @language = 'British'

However, when I attempt to specify 13/12/2015 as a DateTime stored procedure parameter from ASP Classic, I get...

Error converting data type nvarchar to datetime.

From Management Studio, with the correct login, it works.

Why would ASP Classic have this effect? I'm pretty sure it's using the correct connection string.

EDIT:

Its not the connection string. Somehow the date's format is being converted incorrectly before being sent to the database. I can see this from Profiler.

Upvotes: 2

Views: 829

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

Use CONVERT with style:

British/French

103 = dd/mm/yyyy

SELECT CONVERT(DATETIME, '13/12/2015', 103);

-- with stored procedure
DECLARE @d DATETIME = CONVERT(DATETIME, '13/12/2015', 103);

EXEC [dbo].[my_stored_procedure] @d;

Another method is to use ISO 8601 date literal which is culture independent:

The advantage in using the ISO 8601 format is that it is an international standard. Also, datetime values that are specified by using this format are unambiguous. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE settings.

yyyy-mm-ddThh:mm:ss[.mmm]

EXEC [dbo].[my_stored_procedure] '2015-12-13T00:00:00'

Upvotes: 3

Related Questions