Ik krijg de volgende foutmelding bij het uitvoeren van een SQL om mijn gegevenstypewaarde te converteren van varchar naar datetime.
Bericht 242, Level 16, State 3, Line 1
De conversie van een varchar-gegevenstype naar een datetime-gegevenstype resulteerde in een waarde die buiten het bereik ligt.
Ik heb de gegevens gecontroleerd en kan niets vreemds zien: heb de volgende controles uitgevoerd en ze hebben allemaal geen resultaten opgeleverd
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
Is er nog iets anders dat de moeite van het bekijken waard is en misschien tips of hulp bij dit probleem? Ik kom er niet uit.
Antwoord 1, autoriteit 100%
Ik heb een week geleden met hetzelfde probleem te maken gehad.
Het probleem zit in de instelling van de tijdzone. Specificeer in andere formaten zoals mm/dd/jjjj (werkt meestal).
Het specificeren van de datum als 30/12/2013 resulteerde in de fout voor mij. Het specificeren ervan als mm/dd/jjjj-formaat werkte echter.
Als u uw invoer moet converteren, kunt u proberen de CONVERT-methode te gebruiken.
Syntaxis is
CONVERT(VARCHAR,@your_date_Value,103)
CONVERT(VARCHAR, '12/30/2013', 103)
De laatste 103 is het datetime-formaat.
Raadpleeg deze link voor conversie-indelingen en meer informatie.
https://www.w3schools.com/sql/func_sqlserver_convert.asp
Antwoord 2, autoriteit 64%
Ik kwam dit probleem tegen door een domme fout. Zorg ervoor dat de datum echt bestaat!
Bijvoorbeeld:
31 september 2015 bestaat niet.
EXEC dbo.SearchByDateRange @Start = '20150901' , @End = '20150931'
Dit mislukt dus met het bericht:
Error converting data type varchar to datetime.
Voer een geldige datum in om het op te lossen:
EXEC dbo.SearchByDateRange @Start = '20150901' , @End = '20150930'
En het werkt prima.
Antwoord 3, autoriteit 34%
Ik had onlangs een soortgelijk probleem. Regionale instellingen waren correct ingesteld, zowel in de app als in de databaseserver. De uitvoering van SQL resulteerde echter in
“De conversie van een varchar-gegevenstype naar een datetime-gegevenstype resulteerde in een waarde die buiten het bereik ligt”.
Het probleem was de standaardtaal van de db-gebruiker.
Om het in SSMS te controleren of te wijzigen, gaat u naar Beveiliging -> Logins en klik met de rechtermuisknop op de gebruikersnaam van de gebruiker die de query’s uitvoert. Selecteer eigenschappen -> algemeen en zorg ervoor dat de standaardtaal onderaan het dialoogvenster is wat u verwacht.
Herhaal dit voor alle gebruikers die zoekopdrachten uitvoeren.
Antwoord 4, autoriteit 10%
U kunt gebruik maken van
Set dateformat <date-format> ;
in je sp-functie of opgeslagen procedure om dingen voor elkaar te krijgen.
Antwoord 5, autoriteit 5%
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)))
Antwoord 6, autoriteit 2%
Ik heb hetzelfde probleem gehad en heb vastgesteld dat dit probleem zich voordoet omdat SQL Server geen vergelijkingen uitvoert op tekens die op dezelfde manier zijn geconverteerd naar gehele getallen. In mijn test heb ik geconstateerd dat sommige vergelijkingen van geconverteerde karakters, zoals het uitroepteken, typeconversiefouten opleveren, terwijl andere vergelijkingen van geconverteerde karakters, zoals de spatie, buiten het bereik vallen.
Deze voorbeeldcode test de verschillende mogelijke scenario’s en presenteert een oplossing met behulp van geneste REPLACE-instructies. De REPLACE bepaalt of er tekens in de tekenreeks zijn die geen cijfers of de schuine streep zijn, en, indien aanwezig, de lengte van de tekenreeks groter is dan nul, wat aangeeft dat er ‘slechte’ tekens zijn en dat de datum ongeldig is .
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
Uitvoer:
--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
Antwoord 7, autoriteit 2%
+ 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
Antwoord 8, autoriteit 2%
Ook ik ben dit probleem tegengekomen bij het automatisch invoegen van een sysdate in een kolom.
Wat ik deed, is dat ik de datumnotatie van mijn systeem heb gewijzigd zodat deze overeenkomt met de datumnotatie van de SQL-server.
bijv.
mijn SQL-formaat was mm/dd/jjjj en mijn systeemformaat was ingesteld op dd/mm/jjjj.
Ik heb mijn systeemformaat gewijzigd in mm/dd/jjjj en de fout is verdwenen
-kb
Antwoord 9, autoriteit 2%
Zoals u weet, is dit een probleem met de Britse indeling.
U kunt datumconversie indirect doen door de functie te gebruiken.
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
Om deze functie aan te roepen
SELECT dbo.ChangeDateFormatFromUK([dates]) from table
Converteer het normaal naar datetime
SELECT CONVERT(DATETIME,dbo.ChangeDateFormatFromUK([dates])) from table
In jouw geval kun je
SELECT [dates] from table where CONVERT(DATETIME,dbo.ChangeDateFormatFromUK([dates])) > GetDate() -- or any date
Antwoord 10, autoriteit 2%
Test voor jaar > 2079.
Ik ontdekte dat een gebruiker 2106 typte in plaats van 2016 in het jaar (10/12/2106) en boem; dus op 10/12/2016 testte ik en ontdekte dat SQL Server werd geaccepteerd tot 2078, begon die fout te geven als het jaar 2079 of hoger is. Ik heb geen verder onderzoek gedaan naar wat voor soort datumverschuivende SQL Server doet.
Antwoord 11
Ik heb gewoon het varchar-veld dat ik wilde converteren naar een nieuwe tabel (met een DateTime-gearchiveerd) eerst omgezet in een DateTime-compatibele lay-out en daarna zal SQL de conversie van varchar naar DateTime zonder problemen uitvoeren.
In het onderstaande (niet mijn gemaakte tabel met die namen!) maak ik het veld varchar eenvoudig om een DateTime-lookalike te zijn als je dat wilt:
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)
Antwoord 12
Varchar Date Convert to Date and Change the Format
12 november 2016 12:00 , 21/12/2016, 21-12-2016
deze Query werkt voor bovenstaande om te veranderen in dit formaat dd/MM/yyyy
SELECT [Member_ID],[Name] ,
Convert(varchar(50),Convert(date,[DOB],103),103) as DOB
,[NICNO],[Relation] FROM [dbo].[tbl_FamilMember]
Antwoord 13
Deze fout trad bij mij op omdat ik probeerde de minimale datum en tijd in een kolom op te slaan met behulp van inline-query’s rechtstreeks vanuit C#-code.
De datumvariabele is ingesteld op 01/01/0001 12:00:00 AM in de code gezien het feit dat DateTime in C# wordt geïnitialiseerd met deze datum en tijd, tenzij anders ingesteld. En de minst mogelijke datum die is toegestaan in het datatype MS-SQL 2008 datetime is 1753-01-01 12:00:00 AM.
Ik heb de datum van de code gewijzigd en ingesteld op 01/01/1900 en er zijn verder geen fouten gemeld.
Antwoord 14
Ik heb ToString() gebruikt op een datum met mm in plaats van MM.
Antwoord 15
Zorg er wel voor dat uw datums compatibel zijn of correct kunnen worden uitgevoerd in uw databasemanager (bijv. SQL Server Management Studio). De functie DateTime.Now C# is bijvoorbeeld ongeldig in de SQL-server, wat betekent dat uw query geldige functies moet bevatten, zoals GETDATE() voor SQL Server.
Deze wijziging heeft perfect gewerkt voor mij.
Antwoord 16
Enigszins ongebruikelijke oorzaak voor dit probleem, maar voor het geval iemand het nodig heeft. De code waar ik aan werkte, gebruikte:
java.text.DateFormat.getDateTimeInstance()
om een datumnotatie te krijgen. Het opmaakpatroon dat door deze aanroep wordt geretourneerd, is gewijzigd van Java 8 in Java 9, zoals beschreven in dit bugrapport: https://bugs.openjdk.java.net/browse/JDK-8152154 blijkbaar was de opmaak die het voor mij retourneerde niet geschikt voor de database. De oplossing was in plaats daarvan:
DateTimeFormatter.ISO_LOCAL_DATE_TIME
Antwoord 17
Ik heb met dit soortgelijk probleem te maken gehad. Ik denk niet dat veel mensen zullen worden geconfronteerd met de situatie die ik heb meegemaakt. Toch wilde ik mijn ervaring delen. Ik werk in een testomgeving en er waren datetime-gegevens die waren ingesteld op de minimale datetime-waarde 0001/01/01. Deze waarde is minder dan de minimaal mogelijke gegevens voor het datatype smalldatetime. Zorg er dus voor dat u de Min Max-waarde van het datatype controleert.
Voor smalldatetime kun je deze pagina raadplegen: https://docs.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-ver15
Antwoord 18
Bovenaan toevoegen:
SET DATEFORMAT ymd;
of welke indeling u ook gebruikt in uw zoekopdrachten