SQL: la conversione di un tipo di dati varchar in un tipo di dati datetime ha comportato un valore fuori intervallo

Ho riscontrato il seguente errore durante l’esecuzione di un SQL per convertire il valore del tipo di dati da varchar a datetime .

Messaggio 242, livello 16, stato 3, riga 1 La conversione di un tipo di dati varchar in un tipo di dati datetime ha provocato un valore fuori intervallo.

Ho controllato i dati e non vedo nulla di strano: ho eseguito i seguenti controlli e tutti non hanno restituito alcun risultato

 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))  12 SELECT top 100 [Date] , SUBSTRING([date],1,2) from table where convert(int, SUBSTRING([date],4,2))  31 

C’è qualcos’altro che valga la pena guardare e forse valga qualche suggerimento o aiuto con questo problema? Non riesco a capirlo.

Ho affrontato lo stesso problema una settimana fa. Il problema è con l’impostazione del fuso orario. Specificare in altri formati come mm / gg / aaaa (di solito funziona).

Specificare la data come 30/12/2013 ha comportato l’errore per me. Tuttavia, è stato specificato come formato mm / gg / aaaa.

Se hai bisogno di convertire i tuoi input puoi provare a guardare nel metodo CONVERT . La syntax è

CONVERT(VARCHAR,@your_date_Value,103)

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

La finitura 103 è il formato datetime.

Fare riferimento a questo link per i formati di conversione e ulteriori letture. http://www.w3schools.com/sql/func_convert.asp

Mi sono imbattuto in questo problema a causa di un errore stupido. Assicurati che la data esista effettivamente!

Per esempio:

Il 31 settembre 2015 non esiste.

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

Quindi questo fallisce con il messaggio:

 Error converting data type varchar to datetime. 

Per risolvere il problema, inserisci una data valida:

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

E si esegue bene.

Ho avuto un problema simile di recente. Le impostazioni regionali sono state configurate correttamente sia nell’app che nel server di database. Tuttavia, l’esecuzione di SQL ha comportato

“La conversione di un tipo di dati varchar in un tipo di dati datetime ha prodotto un valore fuori intervallo”.

Il problema era la lingua predefinita dell’utente db.

Per controllarlo o modificarlo in SSMS andare su Sicurezza -> Login e fare clic con il tasto destro del mouse sul nome utente dell’utente che esegue le interrogazioni. Seleziona proprietà -> generale e assicurati che la lingua predefinita nella parte inferiore della finestra di dialogo sia quella che ti aspetti.

Ripeti l’operazione per tutti gli utenti che eseguono query.

Puoi fare uso di

 Set dateformat  ; 

nella tua funzione sp o stored procedure per fare le cose.

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

Ho avuto lo stesso problema e ho riscontrato che questo problema si verifica perché SQL Server non esegue confronti tra caratteri convertiti in numeri interi in modo identico. Nel mio test, ho riscontrato che alcuni confronti tra caratteri convertiti, come il punto esclamativo, restituiscono errori di conversione del tipo, mentre altri confronti di caratteri convertiti, come lo spazio, saranno determinati per essere fuori portata.

Questo codice di esempio verifica i diversi scenari possibili e presenta una soluzione utilizzando le istruzioni REPLACE annidate. Il REPLACE determina se nella stringa ci sono dei caratteri che non sono numeri o la barra e, se esistono, la lunghezza della stringa sarà maggiore di zero, indicando che ci sono caratteri “cattivi” e la data non è valida .

 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 

Produzione:

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

Anch’io ho riscontrato questo problema mentre inserivo automaticamente un sysdate in una colonna.

Quello che ho fatto è che ho cambiato il formato della mia data di sistema in modo che corrisponda al formato della data del server SQL. ad esempio il mio formato SQL era mm / gg / aaaa e il mio formato di sistema era impostato su gg / mm / aaaa. Ho cambiato il mio formato di sistema in mm / gg / aaaa e l’errore è andato

-kb

Come sapete, questo è il problema del formato del Regno Unito. Puoi eseguire la conversione della data indirettamente usando la funzione.

 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 

Per chiamare questa funzione

 SELECT dbo.ChangeDateFormatFromUK([dates]) from table 

Convertilo normalmente in datetime

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

Nel tuo caso, puoi farlo

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

Test per l’anno> 2079. Ho rilevato che un utente ha digitato 2106 anziché il 2016 nell’anno (10/12/2106) e boom; così il 10/12/2016 ho testato e trovato SQL Server accettato fino al 2078, ho iniziato a lanciare quell’errore se l’anno è 2079 o superiore. Non ho fatto ulteriori ricerche su quale tipo di data fa scorrere SQL Server.

 + 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 

Ho semplicemente convertito il campo varchar che volevo convertire in una nuova tabella (con un DateTime archiviato) in un layout compatibile DateTime e poi SQL eseguirà la conversione da varchar a DateTime senza problemi.

Nel seguito (non la mia tabella creata con quei nomi!) Rendo semplicemente il campo varchar come un sosia di DateTime se vuoi:

 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) 
 Varchar Date Convert to Date and Change the Format 

12 Nov 2016 12:00, 21/12/2016, 21-12-2016 questa Query Funziona come sopra per cambiare in questo Formato gg / MM / aaaa SELECT [Member_ID],[Name] , Convert(varchar(50),Convert(date,[DOB],103),103) as DOB ,[NICNO],[Relation] FROM [dbo].[tbl_FamilMember]

Questo errore si è verificato per me perché stavo cercando di memorizzare la data e l’ora minima in una colonna utilizzando le query in linea direttamente dal codice C #.

La variabile data è stata impostata su 01/01/0001 12:00:00 nel codice dato il fatto che DateTime in C # viene inizializzato con questa data e ora se non è impostato in modo elso. E la data meno ansible consentita nel datatype MS-SQL 2008 datetime è 1753-01-01 12:00:00 AM.

Ho cambiato la data dal codice e l’ho impostata su 01/01/1900 e non sono stati segnalati ulteriori errori.

Ho usato ToString () in una data con mm anziché MM.

Assicurati che le date siano compatibili o che possano essere eseguite correttamente nel gestore del database (ad es. SQL Server Management Studio). Ad esempio, la funzione DateTime.Now C # non è valida nel server SQL e ciò significa che la query deve includere funzioni valide come GETDATE () per SQL Server.

Questo cambiamento ha funzionato perfettamente per me.