CODEWITHSUNDEEP
sql-serversql-server-2008
urlreader
urlreader

Reputation: 6605

DATEADD error when use French language?

I have a SP which needs to calculate some date & time. It works fine when the language is set as English. However, when it is French, it has this error:

La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Which means datetime overflow based on translation.

The query is similar to:

SET LANGUAGE  'French'

DECLARE @StartPastDays INT
SET @StartPastDays = 1;

DECLARE @PastDays INT
SET @PastDays = 30;

PRINT CONVERT(VARCHAR(10),GETDATE(),111)
PRINT CONVERT(DATE,GETDATE(),111)
PRINT DATEADD(day, -(@PastDays+@StartPastDays-1), CONVERT(VARCHAR(10),GETDATE(),111) )
PRINT DATEADD(day, -(@PastDays+@StartPastDays-1), CAST('2013-07-23' AS DATE))

If I run it, the result is:

Le paramètre de langue est passé à Français.

2013/07/23

2013-07-23

Msg 242, Level 16, State 3, Line 16 La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

2013-06-23

the error happens at: PRINT DATEADD(day, -(@PastDays+@StartPastDays-1), CONVERT(VARCHAR(10),GETDATE(),111) )

Anyone knows why?

Upvotes: 0

Views: 561

Answers (2)

Steve Ford
Steve Ford

Reputation: 7753

DATEADD requires a Date, so just pass GETDATE() or CAST(GETDATE() AS DATE)

SET LANGUAGE  'French'

DECLARE @StartPastDays INT
SET @StartPastDays = 1;

DECLARE @PastDays INT
SET @PastDays = 30;

PRINT CONVERT(VARCHAR(10),GETDATE(),111)
PRINT CONVERT(DATE,GETDATE(),111)
PRINT DATEADD(day, -(@PastDays+@StartPastDays-1), CAST(GETDATE() AS Date))
PRINT DATEADD(day, -(@PastDays+@StartPastDays-1), CAST('2013-07-23' AS DATE))

Upvotes: 1

meda
meda

Reputation: 45490

try like this

SET LANGUAGE  FRENCH
SET DATEFORMAT ymd
GO

Upvotes: 1

Related Questions