CODEWITHSUNDEEP
sqlsql-server
Paul S
Paul S

Reputation: 113

Unable to convert varchar to date

I am trying to convert a function (varchar) to date, but get the below error message.

Error

Failed to convert date and / or time from string.

Script

DECLARE @MyDate date

SET @MyDate = CONVERT(date, dbo.GETMINDATE(0123), 103)

SELECT @MyDate

The function itself returns a varchar date like so - '01 Apr 2010 08:00:00.000'.

The SQL Server host is in French settings, so perhaps it's a regional setting issue.

Upvotes: 0

Views: 127

Answers (3)

Cato
Cato

Reputation: 3701

Here is an experiment

--this returns a value
set language english;
select CAST('01 Apr 2010 08:00:00.000' as date);

--this works
set language french;
select CAST('01 Avr 2010 08:00:00.000' as date);

--this errors out 
set language french;
select CAST('01 Apr 2010 08:00:00.000' as date);

so you can possibly translate the value, according to local settings (which you can vary in the scope of a query).

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453212

If you are stuck with this and can't change this to fix the function to return the correct datatype then you can use PARSE to specify a static culture (so completely unaffected by the locale of the server and the default settings of its users).

SELECT PARSE('01 Apr 2010 08:00:00.000' AS date USING 'en-US')

Obviously replace the string literal with your function call. As you are casting to date in the question I have used the same, this will truncate the time portion.

Use datetime or datetime2 if that was not the intention.

Upvotes: 1

SMor
SMor

Reputation: 2862

So you guessed that it might be a regional or language setting. You guessed correctly. Tibor discusses all facets of the datetime datatype here. Specifically he mentions that the format you use (with the month abbreviation) depends on the language setting in use at runtime. Here is an fiddle to demonstrate.

So how should you fix this? Well, you could force everyone else in the world (every user in your database) to use one of the english language settings. Somehow I don't think that will go over well in the real world. Seems a team meeting might be needed to determine an appropriate path forward. This is a tough lesson to learn about software design.

Upvotes: 0

Related Questions