SQL - Die Konvertierung eines Varchar-Datentyps in einen Datetime-Datentyp führte zu einem Wert außerhalb des Bereichs

Ich lief in dieses Problem, durch einen dummen Fehler. Stellen Sie sicher, dass das Datum wirklich existiert!

Beispiel:

September 31, 2015 nicht vorhanden.

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

Also dies fehl mit der Meldung:

Error converting data type varchar to datetime.

Um es zu beheben, geben Sie ein gültiges Datum:

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

- Und es führt einfach nur gut.

InformationsquelleAutor der Antwort Mr. C

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)))

InformationsquelleAutor der Antwort sonu yadav

Ich habe das gleiche problem und festgestellt, dass dieses Problem tritt auf, weil SQL Server nicht ausführen Vergleiche auf Zeichen, die in Ganzzahlen konvertiert in identischer Weise. In meinem test habe ich festgestellt, dass einige Vergleiche der konvertierten Zeichen, wie das Ausrufezeichen wird return type conversion errors, während andere Vergleiche umgewandelt, Zeichen wie das Leerzeichen, wird bestimmt out of range sein.

Dieser code testet die verschiedenen möglichen Szenarien und präsentiert eine Lösung über verschachtelte REPLACE-Anweisungen. Die ERSETZEN bestimmt, ob alle Zeichen in der Zeichenfolge, die nicht zahlen oder den slash, und, falls vorhanden, wird die Länge der Zeichenkette größer als null ist, wodurch angezeigt wird, dass es "schlechte" Zeichen, und das Datum ist ungültig.

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

Ausgabe:

--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

InformationsquelleAutor der Antwort RSax

Wie Sie wissen, ist dies Großbritannien-format-Problem.
Sie tun können, Datum-Konvertierung indirekt durch die Verwendung der Funktion.

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

Zum aufrufen dieser Funktion

SELECT dbo.ChangeDateFormatFromUK([dates]) from table

Konvertieren es in der Regel zu datetime

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

In Ihrem Fall, die Sie tun können,

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

InformationsquelleAutor der Antwort Sumit

+ 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

InformationsquelleAutor der Antwort shashank m

Varchar Date Convert to Date and Change the Format

Nov 12 2016 12:00 , 21/12/2016, 21-12-2016
diese Abfrage Funktioniert für oben zu ändern, um das Format dd/MM/yyyy

SELECT [Member_ID],[Name] ,
Convert(varchar(50),Convert(date,[DOB],103),103) as DOB
,[NICNO],[Relation] FROM [dbo].[tbl_FamilMember]

InformationsquelleAutor der Antwort Khalid

Ich verwendet ToString() auf ein Datum mit mm statt MM.

InformationsquelleAutor der Antwort L0uis