Sql Datetime throw Error converting data type varchar to datetime

Question

I have this stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET DATEFORMAT DMY -- This line of code was added by my trying to avoid the problem
GO

ALTER PROCEDURE dbo.sp_Hotel_RegistroHuesped 
-- Add the parameters for the stored procedure here
  @p_accion                             int 
, @p_IdRegistroHuesped                  numeric (18,0)
, @p_IntIdHabitacion                    int 
, @p_CheckInFecha                       datetime 
, @p_CheckOutFecha                      datetime
, @p_CheckOutFechaEsperada              datetime 
, @p_NumIdTerceroCondicionesComerciales int 
, @p_StrUsuarioCrea                     usuario  = suser_sname
, @p_DatfechaCrea                       datetime = getdate
, @p_StrUsuarioModifica                 usuario  = NULL
, @p_DatFechaModifica                   datetime = NULL
, @p_ListaHuespedes                     char(400)= null
AS
BEGIN

END

Nothing special or strange to me but really I'm stuck when try to execute like this

set dateformat dmy 

exec dbo.sp_Hotel_RegistroHuesped 
    @p_accion=1,
    @p_IdRegistroHuesped=0,
    @p_IntIdHabitacion=37,
    @p_CheckInFecha='11/03/2014 21:48:28.301',
    @p_ListaHuespedes='',
    @p_CheckOutFecha=NULL,
    @p_CheckOutFechaEsperada='11/03/2014 22:48:28.301',
    @p_NumIdTerceroCondicionesComerciales=1

It always throw

Mens 8114, Nivel 16, Estado 1, Procedimiento sp_Hotel_RegistroHuesped, Línea 0
Error converting data type varchar to datetime

This is a variant that I'm was trying

set dateformat dmy 
exec dbo.sp_Hotel_RegistroHuesped 
    @p_accion=1,
    @p_IdRegistroHuesped=0,
    @p_IntIdHabitacion=37,
    @p_CheckInFecha='11/03/2014 21:48:28',
    @p_ListaHuespedes='',
    @p_CheckOutFecha=NULL,
    @p_CheckOutFechaEsperada='11/03/2014 22:48:28',
    @p_NumIdTerceroCondicionesComerciales=1

Just removing the milliseconds part.

What is the proper way to execute this sp?

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Mar 11, 2014 at 19:38
Who ever names the stored procedures with sp_ should be fired. — Kermit
– Kermit, Commented Mar 11, 2014 at 19:38
Tks a lot, really I removed some parths of the name. But why @FreshPrinceOfSO is so Angry? — Juan Pablo Gomez
– Juan Pablo Gomez, Commented Mar 11, 2014 at 19:41

Szymon · Accepted Answer · 2014-03-11 20:01:48Z

7

As the conversion of the date format you're using ('11/03/2014 22:48:28') depends on the language settings (order of day/month, etc.), it's better to use the ISO standard date format that should be recognised correctly - YYYY-MM-DDTHH:MM:SS:

'2014-03-11T22:48:28'

edited Mar 11, 2014 at 20:01

answered Mar 11, 2014 at 19:40

Szymon

43k16 gold badges99 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Juan Pablo Gomez Over a year ago

Tks for your help... but there is a SET command to configure this option? just because i have several SP calls and others Works great.

Szymon Over a year ago

@JuanPabloGomez See for example this question -stackoverflow.com/questions/10398921/….

Juan Pablo Gomez Over a year ago

Tks a lot Szymon, but at least found the problem, It was at the Sp definition this line @p_DatfechaCrea datetime = getdate doesn't work as I expected. Maybe a collation problem. After remove this line eve3rything Works fine, but Your recomendation is totally valid. i'm going to implement it. TKS

Collectives™ on Stack Overflow

Sql Datetime throw Error converting data type varchar to datetime

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related