Sql Datetime错误地将数据类型varchar转换为Datetime

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

Mens 8114, Nivel 16, Estado 1, processdimiento sp_Hotel_RegistroHuesped, Linea 0错误转换数据类型varchar到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?

执行这个sp的正确方式是什么?

1 个解决方案

#1

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:

由于您所使用的日期格式的转换(“11/03/2014 22:48:28”)取决于语言设置(天/月等的顺序)，所以最好使用ISO标准的日期格式，正确地识别——yyyyyy -MM- ddthh:MM:SS:

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