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?

Solution

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'

Comments

[Juan Pablo Gomez]

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]

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

[Juan Pablo Gomez]

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