Conversion failed when converting character string to smalldatetime data type

Question

Conversion failed when converting character string to smalldatetime data type

alenavafer

SSC-Addicted

Points: 432
More actions
April 10, 2008 at 10:38 am

#379711

Recently i traspased a database from sql server 2000 in spanish to sql server 2005 in english. I change the language of the user sa to spanish and the database is in spanish. . The problem is our dateformat is dmy, i try to force with set dateformat dmy, but always i execute a stored procedure that have a date as input it fail. The error is:
Msg 295, Level 16, State 3, Procedure pa_CalendarioGestion, Line 40
Conversion failed when converting character string to smalldatetime data type.
The code of stored procedure in this line is:
set @diaActual = dateadd("d", @i, @fechaInicio)
This stored procedure in sql server 2000 in spanish not have any problems. For this reason i think the problem is in the configuration.
I hope someone can help me. Thanks.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

What is the data contained in the parameters?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

alenavafer SSC-Addicted Points: 432 More actions · Answer 2

declare @fechaInicio smalldatetime

declare @i int

declare @diaActual smalldatetime

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Now what are the values in the parameters when the function is run like:

Set @fechaInicio = '20080408'

Set @i = 10

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

alenavafer SSC-Addicted Points: 432 More actions · Answer 4

Thanks, but @fechaInicio is a parameter of the stored procedure, and I need that work with the format dd/mm/yyyy, because I have more than 30 stored procedures with this format.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

alenavafer (4/11/2008)
Thanks, but @fechaInicio is a parameter of the stored procedure, and I need that work with the format dd/mm/yyyy, because I have more than 30 stored procedures with this format.

One thing you may need to do is change the double-quotes around the d in the function to single-quotes as by default SQL Server uses single-quotes for character data and double-quotes to contain column names. Here is some code that worked with dd/mm/yyyy format when my server/database is set to mm/dd/yyyy:

DECLARE @d smalldatetime

SET dateformat mdy

SET @d='24/04/2008'--

SELECT @d

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

alenavafer SSC-Addicted Points: 432 More actions · Answer 6

If I put single quote i have an error:

Msg 1023, Level 15, State 1, Procedure pa_CalendarioGestion, Line 34

Invalid parameter 1 specified for datediff.

Msg 1023, Level 15, State 1, Procedure pa_CalendarioGestion, Line 39

Invalid parameter 1 specified for dateadd.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Sorry, my bad. The first paramter, d, needs to be either without any quotes or in double-quotes.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question