[sql] datetime을 삽입하는 동안 문자열에서 날짜 및 / 또는 시간을 변환 할 때 변환에 실패했습니다

다음과 같이 테이블을 만들려고했습니다.

create table table1(date1 datetime,date2 datetime);

먼저 아래와 같이 값을 삽입하려고했습니다.

insert into table1 values('21-02-2012 6:10:00 PM','01-01-2001 12:00:00 AM');

오류가 발생했습니다.

varchar를 datetime으로 변환 할 수 없습니다

그런 다음 stackoverflow에서 제안한 게시물 중 하나로 아래 형식을 시도했습니다.

insert into table1 values(convert(datetime,'21-02-2012 6:10:00 PM',5)
                          ,convert(datetime,'01-01-2001 12:00:00 AM',5));

그러나 여전히 오류가 발생합니다.

문자열에서 날짜 및 / 또는 시간을 변환 할 때 변환에 실패했습니다

어떤 제안?

답변

SQL Server에서 지원하는 형식은 여러 가지가 있습니다 . CAST 및 CONVERT에 대한 MSDN 온라인 설명서를 참조하십시오 . 이러한 형식의 대부분은 어떤 설정을 사용 하느냐에 따라 달라 지므로 이러한 설정은 때때로 작동 할 수도 있고 그렇지 않을 수도 있습니다.

이를 해결하는 방법 은 SQL Server에서 지원 하는 (약간 적응 된) ISO-8601 날짜 형식을 사용 하는 것입니다.이 형식은 SQL Server 언어 및 날짜 형식 설정에 관계없이 항상 작동합니다 .

ISO-8601 형식 SQL Server에서 지원되는 두 가지 유형이있다 :

YYYYMMDD단지 날짜 (시간 부분 없음); 여기에주의하십시오 : 대시 없음! 매우 중요합니다! YYYY-MM-DD이다 NOT 당신의 SQL Server에서 DATEFORMAT 설정의 독립적 인 것 NOT 모든 상황에서 작동!

또는:

YYYY-MM-DDTHH:MM:SS날짜 및 시간-참고 :이 형식 에는 대시가 있지만 생략 할 수 있으며 T의 날짜와 시간 부분을 구분 기호 로 고정 합니다 DATETIME.

이것은 SQL Server 2000 이상에서 유효합니다.

구체적인 경우에는 다음 문자열을 사용하십시오.

insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00');

주의를 기울여야합니다 (참고 : 12 시간 AM / PM 형식 대신 24 시간 국제 형식 을 사용해야합니다 ).

또는 SQL Server 2008 이상을 사용하는 경우 DATETIME2일반 대신 데이터 유형을 사용할 수 있으며 DATETIME현재 INSERT는 문제없이 작동합니다! 🙂 DATETIME2는 변환에서 훨씬 더 좋고 까다 롭지 않으며 SQL Server 2008 이상에서 권장되는 날짜 / 시간 데이터 형식입니다.

SELECT
   CAST('02-21-2012 6:10:00 PM' AS DATETIME2),     -- works just fine
   CAST('01-01-2012 12:00:00 AM' AS DATETIME2)   -- works just fine

왜이 주제 전체가 그렇게 까다 롭고 다소 혼란 스러운지 묻지 마십시오. 그것이 바로 그 방식입니다. 그러나 YYYYMMDD형식을 사용하면 SQL Server의 모든 버전과 SQL Server의 모든 언어 및 날짜 형식 설정에 적합해야합니다.

답변

사용 된 날짜 또는 시간 형식으로 인해 SQL Server에서 변환이 실패하는 경우가 있습니다. 이는 시스템에서 허용 할 수없는 잘못된 데이터를 저장하려고하기 때문입니다.

예:

Create Table MyTable (MyDate);

Insert Into MyTable(MyDate) Values ('2015-02-29');

SQL Server는 다음 오류를 발생시킵니다.

Conversion failed when converting date and/or time from character string.

이 오류의 원인은 연도 (2015)에 그러한 날짜 (2 월 29 일)가 없기 때문입니다.

답변

간단한 답변-5는 이탈리아어 “yy”이고 105는 이탈리아어 “yyyy”입니다. 따라서:

SELECT convert(datetime,'21-02-12 6:10:00 PM',5)

제대로 작동하지만

SELECT convert(datetime,'21-02-12 6:10:00 PM',105)

오류가 발생합니다.

마찬가지로,

SELECT convert(datetime,'21-02-2012 6:10:00 PM',5)

오류가 발생합니다.

SELECT convert(datetime,'21-02-2012 6:10:00 PM',105)

작동합니다.

답변

다음과 같이 날짜 형식을 업데이트하십시오.

yyyy-MM-dd hh:MM:ss

그것은 나를 위해 문제를 해결하고 잘 작동합니다

답변

가능할 때마다 문화권 별 날짜 / 시간 리터럴을 피해야 합니다.

날짜 / 시간을 리터럴로 제공하는 몇 가지 보안 형식 이 있습니다 .

에 대한 모든 예 2016-09-15 17:30:00

ODBC ( 실제 유형으로 즉시 처리되므로 즐겨 찾기 )

{ts'2016-09-15 17:30:00'} -타임 스탬프
{d'2016-09-15'} -날짜 만
{t'17:30:00'} -시간 만

ISO8601 ( 어디서나 최고 )

'2016-09-15T17:30:00'– T중간에 유의 하십시오!

분리되지 않음 (숫자로 잘못 해석 될 수있는 작은 위험)

'20160915' – 순수한 날짜 에만

주의 사항 : 유효하지 않은 날짜는 이상한 오류 로 표시되는 경향이 있습니다.

6 월 31 일 또는 2 월 30 일은 없습니다 …

이상한 변환 오류가 발생하는 또 다른 이유 : 실행 순서!

SQL-Server는 예상치 못한 실행 순서로 작업을 수행하는 것으로 잘 알려져 있습니다. 귀하의 서면 진술은 일부 유형 관련 작업 이 수행 되기 전에 변환이 완료된 것처럼 보이지만 엔진은 이후 단계에서 변환을 수행하기로 결정합니다.

다음은 예제를 통해 설명하는 훌륭한 기사입니다. Rusano.com : “t-sql-functions-do-no-imply-a-certain-order-of-execution” 및 관련 질문이 있습니다.

답변

가장 좋은 방법은이 코드입니다

"select * from [table_1] where date between convert(date,'" + dateTimePicker1.Text + "',105) and convert(date,'" + dateTimePicker2.Text + "',105)"

답변

convert(datetime2,((SUBSTRING( ISNULL(S2.FechaReal,e.ETA),7,4)+'-'+ SUBSTRING( ISNULL(S2.FechaReal,e.ETA),4,2)+'-'+ SUBSTRING( ISNULL(S2.FechaReal,e.ETA),1,2) + ' 12:00:00.127')))  as fecha,