SQL - varchar 데이터 유형을 datetime 데이터 유형으로 변환하여 값이 범위를 벗어났습니다.

SQL - varchar 데이터 유형을 datetime 데이터 유형으로 변환하여 값이 범위를 벗어났습니다.

유형 할 때 했습니다.varchardatetime.

Msg 242, 레벨 16, 상태 3, 라인 1 바하 데이터 유형을 날짜 시간 데이터 유형으로 변환하면 범위를 벗어난 값이 발생했습니다.

데이터를 확인했는데 이상한 내용이 보이지 않습니다. 다음 검사를 실행했지만 모두 결과를 반환하지 않았습니다.

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

이 문제에 대해 살펴볼 가치가 있고 조언이나 도움이 될 만한 다른 것이 있습니까?그것을 이해할 수 없을 것 같습니다.

저는 일주일 전에 같은 문제에 직면한 적이 있습니다.시간대 설정에 문제가 있습니다.mm/dd/yyyy와 같은 다른 형식으로 지정합니다(일반적으로 작동함).

2013년 12월 30일로 날짜를 지정한 결과 오류가 발생했습니다.그러나 mm/dd/yyyy 형식으로 지정하면 작동했습니다.

입력 내용을 변환해야 하는 경우 다음을 확인할 수 있습니다.CONVERT구방. 은 문은입니다.

CONVERT(VARCHAR,@your_date_Value,103)

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

마감 103은 날짜/시간 형식입니다.

변환 형식 및 자세한 내용은 이 링크를 참조하십시오.https://www.w3schools.com/sql/func_sqlserver_convert.asp

저는 어리석은 실수로 인해 이 문제에 부딪혔습니다.날짜가 실제로 존재하는지 확인합니다!

예:

2015년 9월 31일은 존재하지 않습니다.

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

다음과 같은 메시지와 함께 실패합니다.

Error converting data type varchar to datetime.

이 문제를 해결하려면 올바른 날짜를 입력하십시오.

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

그리고 그것은 아주 잘 실행됩니다.

저도 최근에 비슷한 문제가 있었습니다.앱과 데이터베이스 서버 모두에서 국가별 설정이 올바르게 설정되었습니다.그러나 SQL의 실행으로 인해

"varchar 데이터 유형을 datetime 데이터 유형으로 변환하여 값이 범위를 벗어났습니다."

문제는 db 사용자의 기본 언어였습니다.

SSMS에서 확인하거나 변경하려면 Security -> Logins(로그인)로 이동하여 쿼리를 실행하는 사용자의 사용자 이름을 마우스 오른쪽 단추로 클릭합니다.Properties -> general을 선택하고 대화상자 하단의 기본 언어가 원하는 언어인지 확인합니다.

쿼리를 실행하는 모든 사용자에 대해 이 작업을 반복합니다.

을 활용할 수 있습니다.

Set dateformat <date-format> ;

작업을 수행하기 위한 저장 프로시저 또는 yousp 함수에서.

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

맨 위에 추가:

SET DATEFORMAT ymd; 

또는 쿼리에서 사용 중인 형식입니다.

아시다시피 이것은 영국 포맷 문제입니다.함수를 사용하여 날짜 변환을 간접적으로 수행할 수 있습니다.

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

이 함수를 호출하려면

SELECT dbo.ChangeDateFormatFromUK([dates]) from table

정상적으로 날짜 시간으로 변환

SELECT CONVERT(DATETIME,dbo.ChangeDateFormatFromUK([dates])) FROM TABLE

당신의 경우, 당신은 할 수 있습니다.

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

SQL이 때때로 dd/mm/yyyy 형식을 인식하지 못하기 때문에 이 문제가 발생합니다.한 날짜인지 에 따라 . 할 수 있는지를 . 는 서우항리입확상문로아변날닌그래그지다합따고니환 mm/dddyyyyyyyyyyyyy야해에서 . 그래서, 나는 그것이 어떻게 이루어질 수 있는지 아래에 보여주었고, 나는 다시 정렬할 수 있는 함수를 만들었습니다.mm/dd/yyyydd/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

저도 같은 문제를 겪었고 SQL Server가 동일한 방식으로 정수로 변환된 문자를 비교하지 않기 때문에 이 문제가 발생한다고 판단했습니다.테스트에서 느낌표와 같은 일부 변환 문자 비교는 형식 변환 오류를 반환하는 반면 공백과 같은 다른 변환 문자 비교는 범위를 벗어난 것으로 확인됩니다.

이 샘플 코드는 가능한 여러 시나리오를 테스트하고 중첩된 REPLACE 문을 사용하여 솔루션을 제공합니다.REPLACE는 문자열에 숫자나 슬래시가 아닌 문자가 있는지 확인하며, 문자열 길이가 0보다 클 경우 '잘못된' 문자가 있고 날짜가 잘못되었음을 나타냅니다.

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

출력:

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

열에 시스템 날짜를 자동으로 삽입하는 동안에도 이 문제가 발생했습니다.

SQL 서버의 날짜 형식과 일치하도록 시스템 날짜 형식을 변경했습니다. 예를 들어 SQL 형식은 mm/dd/yyyy이고 시스템 형식은 dd/mm/yyyy로 설정되었습니다.시스템 형식을 mm/dd/yyyyy로 변경했는데 오류가 발생하지 않았습니다.

-kb

2079년 이후의 연도에 대한 테스트.한 사용자가 2016년(10/12/2106)이 아닌 2106을 입력한 것을 발견하고 2016년 10/12/2016에 SQL Server를 테스트한 결과 2078년까지 허용되었으며 연도가 2079년 이상인 경우 해당 오류를 발생시키기 시작했습니다.sliding SQL Server가 어떤 날짜에 하는지에 대해서는 더 이상 조사하지 않았습니다.

저도 비슷한 문제가 있었는데, 조사한 결과 데이터베이스에 매우 오래된 날짜가 있다는 것을 발견했습니다.

따라서 date에서 datetime으로 변환하는 것은 1753-01-01(포함) 이후의 날짜에 대해서만 가능하다는 것을 알게 되었습니다.

select CONVERT(Datetime, '1753-01-01', 103) as RESULT
--1753-01-01 00:00:00.000

select CONVERT(Datetime, '1752-12-31', 103) as RESULT
--The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

새 테이블(DateTime 파일이 있는)로 변환하려는 varchar 필드를 DateTime 호환 레이아웃으로 먼저 변환한 다음 SQL이 문제 없이 varchar에서 DateTime으로 변환합니다.

아래에서 (이러한 이름을 가진 내가 만든 테이블이 아님!) 다음과 같이 하려면 단순히 바차 필드를 날짜/시간과 같은 모양으로 만듭니다.

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

2016년 11월 12일 12:00, 21/12/2016, 21-12-2016 이 쿼리는 위에서 이 형식 dd/MM/yyyy로 변경할 수 있습니다. SELECT [Member_ID],[Name] , Convert(varchar(50),Convert(date,[DOB],103),103) as DOB ,[NICNO],[Relation] FROM [dbo].[tbl_FamilMember]

C# 코드에서 직접 인라인 쿼리를 사용하여 최소 날짜와 시간을 열에 저장하려고 했기 때문에 이 오류가 발생했습니다.

C#의 DateTime이 별도로 설정되지 않은 경우 이 날짜와 시간으로 초기화된다는 사실을 고려하여 코드에서 date 변수는 01/01/0001 12:00:00 AM으로 설정되었습니다.그리고 MS-SQL 2008 날짜/시간 데이터 유형에서 허용되는 최소 날짜는 1753-01-01 오전 12:00입니다.

코드에서 날짜를 변경하여 01/01/1900로 설정하였고 추가 오류는 보고되지 않았습니다.

저는 MM 대신에 mm가 있는 날짜에 ToString()을 사용했습니다.

데이터베이스 관리자(예: SQL Server Management Studio)에서 날짜가 호환되거나 제대로 실행되는지 확인하십시오.예를 들어, 날짜 시간입니다.이제 SQL Server에서 C# 함수가 유효하지 않습니다. 즉, 쿼리에 SQL Server용 GETDATE()와 같은 유효한 함수가 포함되어야 합니다.

이 변화는 저에게 완벽하게 효과가 있었습니다.

이 문제에 대한 약간 특이한 원인이지만 필요한 사람이 있을 경우를 대비해서입니다.제가 작업하던 코드는 다음과 같습니다.

java.text.DateFormat.getDateTimeInstance()

날짜 정보를 얻기 위해.이 호출에 의해 반환된 형식 지정 패턴이 이 버그 보고서에 설명된 대로 Java 8에서 Java 9로 변경되었습니다. https://bugs.openjdk.java.net/browse/JDK-8152154 은 제게 반환된 형식이 데이터베이스에 적합하지 않은 것 같습니다.대신 솔루션은 다음과 같습니다.

DateTimeFormatter.ISO_LOCAL_DATE_TIME

저는 이와 비슷한 문제에 직면했습니다.저는 많은 사람들이 제가 직면한 상황에 직면할 것이라고 생각하지 않습니다.그럼에도 불구하고 저는 여전히 제 경험을 공유하고 싶었습니다.저는 테스트 환경에서 일하고 있으며, 최소 datetime 값 0001/01로 설정된 datetime 데이터가 있었습니다.이 값은 작은 날짜/시간 데이터 유형에 대해 가능한 최소 데이터보다 작습니다.따라서 데이터 유형의 최소 최대값을 확인해야 합니다.짧은 시간 동안 다음 페이지를 참조할 수 있습니다. https://learn.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-ver15

언급URL : https://stackoverflow.com/questions/20838344/sql-the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in