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

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

---

내 데이터 유형 값을에서 varchar으로 변환하기 위해 SQL을 실행할 때 다음 오류가 발생 했습니다 datetime.

Msg 242, 수준 16, 상태 3, 줄 1 varchar 데이터 형식을 datetime 데이터 형식으로 변환하면 값이 범위를 벗어났습니다.

데이터를 확인했는데 이상한 것이 보이지 않습니다. 다음 확인을 실행했는데 모두 결과가 반환되지 않았습니다.

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에서 확인하거나 변경하려면 보안-> 로그인으로 이동하여 쿼리를 실행하는 사용자의 사용자 이름을 마우스 오른쪽 버튼으로 클릭합니다. 속성-> 일반을 선택하고 대화 상자 하단의 기본 언어가 예상 한 언어인지 확인합니다.

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

---

당신은 사용할 수 있습니다

Set dateformat <date-format> ;

sp 함수 또는 저장 프로 시저에서 작업을 완료합니다.

---

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

---

동일한 문제가 발생했으며 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

---

열에 sysdate를 자동으로 삽입하는 동안이 문제가 발생했습니다.

내가 한 일은 SQL 서버의 날짜 형식과 일치하도록 시스템 날짜 형식을 변경 한 것입니다. 예를 들어 내 SQL 형식은 mm / dd / yyyy이고 시스템 형식은 dd / mm / yyyy로 설정되었습니다. 시스템 형식을 mm / dd / yyyy로 변경했는데 오류가 사라졌습니다.

-kb

---

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

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

일반적으로 datetime으로 변환

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

귀하의 경우에는 할 수 있습니다

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

---

2079 년을 초과하는 해에 대한 테스트. 사용자가 2016 년 (10/12/2106) 대신 2106을 입력 한 것을 발견했습니다. 그래서 2016 년 10 월 12 일에 테스트 한 결과 SQL Server가 2078 년까지 허용되는 것으로 나타 났으며, 연도가 2079 년 이상이면 해당 오류가 발생하기 시작했습니다. 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

---

I simply converted the varchar field that I wanted to convert into a new table (with a DateTime filed) to a DateTime compatible layout first and then SQL will do the conversion from varchar to DateTime without problems.

In the below (not my created table with those names !) I simply make the varchar field to be a DateTime lookalike if you want to:

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

Nov 12 2016 12:00 , 21/12/2016, 21-12-2016 this Query Works for above to change to this Format dd/MM/yyyy SELECT [Member_ID],[Name] , Convert(varchar(50),Convert(date,[DOB],103),103) as DOB ,[NICNO],[Relation] FROM [dbo].[tbl_FamilMember]

---

This error occurred for me because i was trying to store the minimum date and time in a column using inline queries directly from C# code.

The date variable was set to 01/01/0001 12:00:00 AM in the code given the fact that DateTime in C# is initialized with this date and time if not set elsewise. And the least possible date allowed in the MS-SQL 2008 datetime datatype is 1753-01-01 12:00:00 AM.

I changed the date from the code and set it to 01/01/1900 and no errors were reported further.

---

I used ToString() on a date with mm instead of MM.

---

Just make sure that your Dates are compatible or can be run properly in your database manager(e.g. SQL Server Management Studio). For example, the DateTime.Now C# function is invalid in SQL server meaning your query has to include valid functions like GETDATE() for SQL Server.

This change has worked perfectly for me.

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