sql-server - texto - convertir una cadena a entero en sql

SQL: la conversión de un tipo de datos varchar a un tipo de datos de fecha y hora dio como resultado un valor fuera de rango

Question

He estado recibiendo el siguiente error al ejecutar un SQL para convertir mi valor de tipo de datos de varchar a datetime .

Msg 242, nivel 16, estado 3, línea 1 La conversión de un tipo de datos varchar a un tipo de datos de fecha y hora dio como resultado un valor fuera de rango.

He comprobado los datos y no puedo ver nada raro: ejecuté las siguientes comprobaciones y no devolví resultados

SELECT [Date] from table where [DATe] is null
SELECT [Date] from table where [DATe] = ''''
SELECT [Date] from table where LEN([date])> 10
SELECT [Date] from table where LEN([date])< 10
SELECT top 100 [Date] , SUBSTRING([date],4,2) from [table where convert(int, SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 12
SELECT top 100 [Date] , SUBSTRING([date],1,2) from table where convert(int, SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 31

¿Hay algo más que valga la pena mirar y que tal vez valga la pena con cualquier sugerencia o ayuda con este problema? Parece que no puede tocar fondo.

Answer 1

Como saben, esto es un problema de formato del Reino Unido. Puede hacer la conversión de fecha indirectamente mediante el uso de la función.

CREATE FUNCTION  ChangeDateFormatFromUK
( 
   @DateColumn varchar(10)
)

RETURNS VARCHAR(10)
AS 
 BEGIN
    DECLARE @Year varchar(4), @Month varchar(2), @Day varchar(2), @Result varchar(10)
    SET @Year = (SELECT substring(@DateColumn,7,10))
    SET @Month = (SELECT substring(@DateColumn,4,5)) 
    SET @Day = (SELECT substring(@DateColumn,1,2))
   SET @Result  = @Year  + ''/'' @Month + ''/'' +  @Day

 RETURN @Result
END

Para llamar a esta función

SELECT dbo.ChangeDateFormatFromUK([dates]) from table

Convierta normalmente a datetime

SELECT CONVERT(DATETIME,dbo.ChangeDateFormatFromUK([dates])) from table

En tu caso, puedes hacer

SELECT [dates] from table where CONVERT(DATETIME,dbo.ChangeDateFormatFromUK([dates])) > GetDate()   -- or any date

Answer 2

He enfrentado el mismo problema hace una semana. El problema es con la configuración de la zona horaria. Especifíquelo en otros formatos como mm / dd / aaaa (normalmente funciona).

Especificar la fecha como 30/12/2013 resultó en el error para mí. Sin embargo, especificarlo como formato mm / dd / aaaa funcionó.

Si necesita convertir su entrada, puede intentar buscar en el método CONVERT . La sintaxis es

CONVERT(VARCHAR,@your_date_Value,103)

CONVERT(VARCHAR, ''12/30/2013'', 103)

El acabado 103 es el formato de fecha y hora.

Consulte este enlace para obtener formatos de conversión y lecturas adicionales. http://www.w3schools.com/sql/func_convert.asp

Answer 3

He tenido el mismo problema y he determinado que este problema surge porque SQL Server no realiza comparaciones en los caracteres convertidos a enteros de manera idéntica. En mi prueba, he encontrado que algunas comparaciones de caracteres convertidos, como el signo de exclamación, devolverán errores de conversión de tipo, mientras que otras comparaciones de caracteres convertidos, como el espacio, se determinarán fuera de rango.

Este código de muestra prueba los diferentes escenarios posibles y presenta una solución usando declaraciones REEMPLAZADAS anidadas. El REPLACE determina si hay caracteres en la cadena que no son numerales o la barra, y si existe, la longitud de la cadena será mayor que cero, lo que indica que hay caracteres "incorrectos" y la fecha no es válida. .

DECLARE @str varchar(10)
SET @str = ''12/10/2012''
IF convert(int, substring(@str,4,2)) <= 31 AND convert(int, substring(@str,4,2)) >= 1
    PRINT @str+'': Passed Test''
    ELSE PRINT @str+'': Failed Test''
GO

DECLARE @str varchar(10)
SET @str = ''12/10/2012'' 
PRINT ''Number of characters in '' + @str + '' that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): '' + convert(varchar(5),len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@str,''0'',''''),''1'',''''),''2'',''''),''3'',''''),''4'',''''),''5'',''''),''6'',''''),''7'',''''), ''8'',''''),''9'',''''),''/'',''''),'' '',''+''))) --replace space with a + to avoid empty string
PRINT ''''
GO

DECLARE @str varchar(10)
SET @str = ''12/!0/2012''
    IF convert(int, substring(@str,4,2)) <= 31 AND convert(int, substring(@str,4,2)) >= 1
        PRINT @str+'': Passed Test''
        ELSE PRINT @str+'': Failed Test''
GO

DECLARE @str varchar(10)
SET @str = ''12/!0/2012'' 
PRINT ''Number of characters in '' + @str + '' that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): '' + convert(varchar(5),len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@str,''0'',''''),''1'',''''),''2'',''''),''3'',''''),''4'',''''),''5'',''''),''6'',''''),''7'',''''), ''8'',''''),''9'',''''),''/'',''''),'' '',''+''))) --replace space with a + to avoid empty string
PRINT ''''
GO

DECLARE @str varchar(10)
SET @str = ''12/  /2012''
IF convert(int, substring(@str,4,2)) <= 31 AND convert(int, substring(@str,4,2)) >= 1
    PRINT @str+'': Passed Test''
    ELSE PRINT @str+'': Failed Test''
GO

DECLARE @str varchar(10)
SET @str = ''12/  /2012'' 
PRINT ''Number of characters in '' + @str + '' that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): '' + convert(varchar(5),len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@str,''0'',''''),''1'',''''),''2'',''''),''3'',''''),''4'',''''),''5'',''''),''6'',''''),''7'',''''), ''8'',''''),''9'',''''),''/'',''''),'' '',''+''))) --replace space with a + to avoid empty string

Salida:

--Output
--12/10/2012: Passed Test
--Number of characters in 12/10/2012 that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): 0

--Msg 245, Level 16, State 1, Line 4
--Conversion failed when converting the varchar value ''!0'' to data type int.
--Number of characters in 12/!0/2012 that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): 1

--12/  /2012: Failed Test
--Number of characters in 12/  /2012 that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): 2

Answer 4

Me encontré con este problema debido a un error tonto. ¡Asegúrate de que la fecha realmente exista!

Por ejemplo:

31 de septiembre de 2015 no existe.

EXEC dbo.SearchByDateRange @Start = ''20150901'' , @End = ''20150931''

Entonces esto falla con el mensaje:

Error converting data type varchar to datetime.

Para solucionarlo, ingrese una fecha válida:

EXEC dbo.SearchByDateRange @Start = ''20150901'' , @End = ''20150930''

Y se ejecuta bien.

Answer 5

Prueba para el año> 2079. Descubrí que un usuario tipeó 2106 en lugar de 2016 en el año (10/12/2106) y auge; así que el 12/10/2016 probé y encontré SQL Server aceptado hasta 2078, comencé a lanzar ese error si el año es 2079 o superior. No he hecho más investigaciones sobre qué tipo de fecha desliza SQL Server.

Answer 6

Puedes hacer uso de

Set dateformat <date-format> ;

en tu sp función o procedimiento almacenado para hacer las cosas.

Answer 7

Se produjo este error porque estaba tratando de almacenar la fecha y hora mínima en una columna usando consultas en línea directamente desde el código C #.

La variable de fecha se configuró en 01/01/0001 12:00:00 AM en el código dado el hecho de que DateTime en C # se inicializa con esta fecha y hora si no se establece de otra manera. Y la menor fecha posible permitida en el tipo de datos datetime de MS-SQL 2008 es 1753-01-01 12:00:00 AM.

Cambié la fecha del código y lo configuré al 01/01/1900 y no se informaron más errores.

Answer 8

Simplemente convertí el campo varchar que quería convertir en una nueva tabla (con un DateTime archivado) primero en un diseño compatible con DateTime y luego SQL hará la conversión de varchar a DateTime sin problemas.

En el cuadro siguiente (¡no en mi tabla creada con esos nombres!) Simplemente hago que el campo varchar sea similar a DateTime si quieres:

update report1455062507424 
set [Move Time] = substring([Move Time], 7, 4) + ''-''+ substring([Move Time], 4, 2) + ''-''+ substring([Move Time], 1, 2) + '' '' + 
    substring([Move Time], 12, 5)  

Answer 9

Solo asegúrese de que sus fechas sean compatibles o se puedan ejecutar correctamente en su gestor de bases de datos (por ejemplo, SQL Server Management Studio). Por ejemplo, la función DateTime.Now C # no es válida en el servidor SQL, lo que significa que su consulta debe incluir funciones válidas como GETDATE () para SQL Server.

Este cambio me funcionó a la perfección.

Answer 10

Tuve un problema similar recientemente. La configuración regional se configuró correctamente tanto en la aplicación como en el servidor de la base de datos. Sin embargo, la ejecución de SQL dio como resultado

"La conversión de un tipo de datos varchar a un tipo de datos de fecha y hora dio como resultado un valor fuera de rango".

El problema era el idioma predeterminado del usuario db.

Para verificarlo o cambiarlo en SSMS, vaya a Seguridad -> Inicios de sesión y haga clic con el botón secundario en el nombre de usuario del usuario que ejecuta las consultas. Seleccione propiedades -> general y asegúrese de que el idioma predeterminado en la parte inferior del cuadro de diálogo sea el que espera.

Repita esto para todos los usuarios que ejecutan consultas.

Answer 11

Usé ToString () en una fecha con mm en lugar de MM.

Answer 12

Yo también encontré este problema al insertar automáticamente un sysdate en una columna.

Lo que hice fue cambiar el formato de fecha de mi sistema para que coincidiera con el formato de fecha del servidor SQL. por ejemplo, mi formato SQL era mm / dd / aaaa y el formato de mi sistema estaba configurado en dd / mm / aaaa. Cambié el formato de mi sistema a mm / dd / aaaa y el error desapareció

-kb

Answer 13

+ this happens because sql sometimes doesn''t recognize dd/mm/yyyy format
+ so we should always check if the input string is a valid date or not and the accordingly convert it to mm/dd/yyyy and so , i have shown below how it can be done, i have created a function to rearrange in mm/dd/yyyy from dd/mm/yyyy

select case when isdate(''yourdate'')=1 then CAST(''yourdate'' AS datetime) 
  else (select * from dbo.fn_convertdate(yourdate))

Create function dbo.fn_convertdate( @Stringdate nvarchar(29))
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
)
Begin
Declare @table table(id int identity(1,1), data varchar(255))
Declare @firstpart nvarchar(255)
Declare @tableout table(id int identity(1,1), data varchar(255))

Declare @Secondpart nvarchar(255)
Declare @Thirdpart nvarchar(255)

declare @date datetime

insert into @table
select * from dbo.fnSplitString(@Stringdate,''/'')
select @firstpart=data from @table where id=2
select @Secondpart=data from @table where id=1
select @Thirdpart=data from @table where id=3
set @date=@firstpart+''/''+@Secondpart+''/''+@Thirdpart
insert into @output(splitdata) values(
@date)


return
End

Answer 14

Create procedure [dbo].[a]

@examdate varchar(10) ,
@examdate1 varchar(10)
AS
Select tbl.sno,mark,subject1,
Convert(varchar(10),examdate,103) from tbl
where 
(Convert(datetime,examdate,103)  >= Convert(datetime,@examdate,103) 
and (Convert(datetime,examdate,103) <=  Convert(datetime,@examdate1,103)))

Answer 15

Varchar Date Convert to Date and Change the Format

12 de noviembre 2016 12:00, 21/12/2016, 21-12-2016 esta consulta funciona para arriba para cambiar a este formato dd / SELECT [Member_ID],[Name] , Convert(varchar(50),Convert(date,[DOB],103),103) as DOB ,[NICNO],[Relation] FROM [dbo].[tbl_FamilMember] / aaaa SELECT [Member_ID],[Name] , Convert(varchar(50),Convert(date,[DOB],103),103) as DOB ,[NICNO],[Relation] FROM [dbo].[tbl_FamilMember]