select *
from A
where posted_date >= '2015-07-27 00:00:00.000'
and posted_date <= '2015-07-27 23:59:59.999'그러나 결과에는 오늘 게시 날짜 : 2015-07-28의 레코드가 포함됩니다. 내 데이터베이스 서버가 내 국가에 없습니다. 무엇이 문제입니까?
답변
datetime데이터 유형 을 사용하고 있으므로 SQL Server가 날짜 및 시간 데이터를 반올림하는 방법을 이해해야합니다.
╔═══════════╦═════╦═════════════════════════════╦═════════════════════════════╦══════════╦═══════════╗
║ Name ║ sn ║ Minimum value ║ Maximum value ║ Accuracy ║ Storage ║
╠═══════════╬═════╬═════════════════════════════╬═════════════════════════════╬══════════╬═══════════╣
║ datetime ║ dt ║ 1753-01-01 00:00:00.000 ║ 9999-12-31 23:59:59.997 ║ 3.33 ms ║ 8 bytes ║
║ datetime2 ║ dt2 ║ 0001-01-01 00:00:00.0000000 ║ 9999-12-31 23:59:59.9999999 ║ 100ns ║ 6-8 bytes ║
╚═══════════╩═════╩═════════════════════════════╩═════════════════════════════╩══════════╩═══════════╝
아래 쿼리를 사용하면 DATETIME데이터 유형 을 사용할 때 SQL Server가 수행하는 반올림 문제를 쉽게 확인할 수 있습니다 .
select '2015-07-27 00:00:00.000' as Original_startDateTime,
convert(datetime ,'2015-07-27 00:00:00.000') as startDateTime,
'2015-07-27 23:59:59.999' as Original_endDateTime,
convert(datetime ,'2015-07-27 23:59:59.999') as endDateTime,
'2015-07-27 00:00:00.000' as Original_startDateTime2,
convert(datetime2 ,'2015-07-27 00:00:00.000') as startDateTime2, -- default precision is 7
'2015-07-27 23:59:59.999' as Original_endDateTime2,
convert(datetime2 ,'2015-07-27 23:59:59.999') as endDateTime2 -- default precision is 7
DATETIME2SQL Server 2008부터 사용되었으므로 대신 대신 사용하십시오 DATETIME. 상황의 경우, 사용할 수 datetime2와 함께 3 소수의 정밀도 예 datetime2(3).
사용의 장점 datetime2:
- 대 시간 구성 요소에 대한 7 소수점을 지원
datetime에만 3 소수점을 지원하는 .. 그리고, 따라서 당신은 기본으로하기 때문에 반올림 문제를 참조datetime라운드 가장 가까운.003 seconds단위로.000,.003또는.007초. datetime2보다 훨씬 더 정확datetime하고datetime2당신이를 제어 할 수 있습니다DATE와TIME같은 반대datetime.
참고 :
답변
다른 사람들이 귀하의 질문에 대한 의견과 다른 답변에서 언급했듯이 핵심 문제는 SQL Server 2015-07-27 23:59:59.999에 2015-07-28 00:00:00.000의해 반올림됩니다 . 문서 당 을 위해 DATETIME:
시간 범위-00:00:00 ~ 23 : 59 : 59.997
시간 범위는 절대로 될 수 없습니다.999 . 또한 설명서에서 SQL Server가 최소 유효 숫자에 사용하는 반올림 규칙을 지정합니다.
최하위 숫자는 “0”, “3”또는 “7”의 세 가지 가능한 값 중 하나만 가질 수 있습니다.
사용할 수있는 몇 가지 솔루션 / 해결 방법이 있습니다.
-- Option 1
SELECT
*
FROM A
WHERE posted_date >= '2015-07-27 00:00:00.000'
AND posted_date < '2015-07-28 00:00:00.000' --Round up and remove equality
-- Option 2
SELECT
*
FROM A
WHERE posted_date >= '2015-07-27 00:00:00.000'
AND posted_date <= '2015-07-27 23:59:59.997' --Round down and keep equality
-- Option 3
SELECT
*
FROM A
WHERE CAST(posted_date AS DATE) = '2015-07-27' -- Use different data type
-- Option 4
SELECT
*
FROM A
WHERE CONVERT(CHAR(8), DateColumn, 112) = '20150727' -- Cast to string stripping off time
-- Option 5
SELECT
*
FROM A
WHERE posted_date BETWEEN '2015-07-27 00:00:00.000'
AND '2015-07-27 23:59:59.997' --Use between위에서 제시 한 5 가지 옵션 중 옵션 1과 3을 유일하게 실행 가능한 옵션으로 간주합니다. 그들은 의도를 명확하게 전달하며 데이터 유형을 업데이트해도 중단되지 않습니다. SQL Server 2008 이상을 사용하는 경우 옵션 3이 선호되는 방법이라고 생각합니다. 당신이 사용에서 멀리 변경할 수있는 경우 즉 특히 사실이다 DATETIMEA와 데이터 유형을 DATE당신을위한 데이터 형식 posted_date열입니다.
옵션 3과 관련하여 몇 가지 문제에 대한 아주 좋은 설명은 여기에서 찾을 수 있습니다 .
.997소수 초는 사람들이 “수정”하고 싶은 또 다른 마법의 숫자 일 것이므로 옵션 2와 5를 좋아하지 않습니다 . 왜 더 BETWEEN널리 수용되지 않는지 더 많은 이유로, 이 게시물 을 확인하고 싶을 수도 있습니다 .
비교 목적으로 데이터 유형을 문자열로 변환하면 나쁘게 느껴지므로 옵션 4가 마음에 들지 않습니다. SQL Server에서이를 피하기위한보다 정성적인 이유 는 인덱스 검색을 수행 할 수 없어 성능이 저하 될 수 있기 때문에 Sargability에 영향을 미치기 때문입니다.
날짜 범위 쿼리를 처리하는 올바른 방법과 잘못된 방법에 대한 자세한 내용은 Aaron Bertrand 의이 게시물 을 확인 하십시오 .
이별에서는 원래 쿼리를 유지할 수 있으며 posted_date열을 a에서 a DATETIME로 변경하면 원하는대로 작동 합니다 DATETIME2(3). 이는 서버의 저장 공간을 절약하고 동일한 정밀도로 더 높은 정확도를 제공하며 표준을 준수 / 휴대 할 수 있으며 향후 요구가 변경 될 경우 정확도 / 정밀도를 쉽게 조정할 수 있습니다. 그러나 SQL Server 2008 이상을 사용하는 경우에만 옵션입니다.
약간의 사소한 일이지만 1/300두 번째 정확도 는이 StackOverflow 답변에 따라DATETIME UNIX에서 보류 되는 것처럼 보입니다 . 공유 헤리티지 가있는 Sybase는 데이터 유형 과 데이터 유형 에서 두 번째 정확도 와 비슷 하지만 가장 작은 자릿수는 “0”, “3”및 “6”에서 터치가 다릅니다. 제 생각 에 두 번째 및 / 또는 3.33ms 정확도는 SQL Server 데이터 형식 의 시간 동안 4 바이트 블록이 1ms 정확도를 쉽게 지원 할 수 있기 때문에 불행한 아키텍처 결정 입니다.1/300DATETIMETIME1/300DATETIME
답변
암시 적 변환
게시 된 날짜 데이터 유형이 Datetime이라고 가정합니다. 그러나 문자열 (Varchar)이 암시 적으로 Datetime으로 변환되므로 다른 쪽의 유형이 Datetime, Datetime2인지 또는 Time인지는 중요하지 않습니다.
posting_date를 Datetime2 (또는 Time)로 선언하면 altough 가 유효한 Datetime2 값 posted_date <= '2015-07-27 23:59:59.99999'이므로 where 절이 실패합니다. 이는 23:59:59.99999유효한 Datetime 값이 아닙니다.
Conversion failed when converting date and/or time from character string.Datetime의 시간 범위
Datetime의 시간 범위는 00:00:00에서 23 : 59 : 59.997입니다. 따라서 23 : 59 : 59.999가 범위를 벗어 났으며 가장 가까운 값으로 반올림 또는 내림해야합니다.
정확성
또한 Datetime 값은 .000, .003 또는 .007 초 단위로 반올림됩니다. (즉, 000, 003, 007, 010, 013, 017, 020, …, 997)
이 2015-07-27 23:59:59.999범위 내에 있는 값이 아닌 경우 : 2015-07-27 23:59:59.997및 2015-07-28 0:00:00.000.
이 범위는 .000, .003 또는 .007로 끝나는 가장 근접한 선행 및 다음 옵션에 해당합니다.
반올림 또는 내림 ?
보다 2015-07-28 0:00:00.000(+1 대 -2)에 가까워 지므로 2015-07-27 23:59:59.997문자열은 반올림되어 다음 Datetime 값이됩니다 2015-07-28 0:00:00.000.
2015-07-27 23:59:59.998(또는 .995, .996, .997, .998) 과 같은 상한을 사용하면 반올림 2015-07-27 23:59:59.997되고 쿼리가 예상대로 작동합니다. 그러나 그것은 해결책이 아니라 운이 좋은 가치 일 것입니다.
Datetime2 또는 시간 유형
Datetime2 및 Time 시간 범위는 100ns의 정확도 (7 자리 정밀도로 사용될 경우 마지막 숫자)를 00:00:00.0000000통과 23:59:59.9999999합니다.
그러나 Datetime (3) 범위는 Datetime 범위와 유사하지 않습니다.
- 날짜 시간
0:0:00.000에23:59:59.997 - Datetime2
0:0:00.000000000~23:59:59.999
해결책
결국 아래 날짜보다 다음 날 아래의 날짜를 찾거나 하루 중 마지막 조각으로 생각한 것과 같은 것이 더 안전합니다. 다음 날은 항상 0 : 00 : 00.000에 시작하지만 다른 데이터 유형은 하루가 끝날 때 같은 시간을 갖지 않을 수 있기 때문입니다.
Datetime `0:0:00.000` to `23:59:59.997`
Datetime2 `0:0:00.000000000` to `23:59:59.999-999-900`
Time2 `0:0:00.000000000` to `23:59:59.999-999-900`< 2015-07-28 0:00:00.000정확한 결과를 제공 하고 최선의 선택입니다<= 2015-07-27 23:59:59.xxx생각한대로 반올림하지 않으면 예기치 않은 값이 반환 될 수 있습니다.- 인덱스 사용을 제한하므로 날짜로 변환 및 함수 사용을 피해야합니다.
[posted_date]를 Datetime2로 변경하면 정밀도가 높아져이 문제를 해결할 수 있지만 문자열이 여전히 Datetime으로 변환되므로 도움이되지 않습니다. 그러나 캐스트가 추가 cast(2015-07-27 23:59:59.999' as datetime2)되면 정상적으로 작동합니다.
캐스트 및 변환
전송은 최대 3 자리 숫자를 Datetime으로 또는 최대 9 자리 숫자를 Datetime2 또는 Time으로 변환하여 올바른 정밀도로 반올림 할 수 있습니다.
Cast of Datetime2 및 Time2는 다른 결과를 제공 할 수 있습니다.
select cast('20150101 23:59:59.999999999' as datetime2(7))반올림 2015-05-03 00 : 00 : 00.0000000 (999999949보다 큰 값의 경우)select cast('23:59:59.999999999' as time(7))=> 23 : 59 : 59.9999999
날짜 시간이 0, 3 및 7 단위로 발생하는 문제를 해결합니다. 그러나 다음 날 1 나노 초 이전의 날짜를 항상 찾는 것이 좋습니다 (항상 0 : 00 : 00.000).
원본 MSDN : datetime (Transact-SQL)
답변
반올림
select cast('2015-07-27 23:59:59.999' as datetime)
returns 2015-07-28 00:00:00.000.998, .997, .996, .995 모두 캐스트 / 라운딩 .997
사용해야합니다
select *
from A
where posted_date >= '2015-07-27 00:00:00.000'
and posted_date < '2015-07-28 00:00:00.000'또는
where cast(posted_date as date) = '2015-07-27'이 링크의 정확성 참조
항상 .000, .003, .007로보고
답변
select * from A where date(posted_date) = '2015-07-27'