datetime 값의 시간 부분을 제거하는 방법 (SQL Server)?

datetime 값의 시간 부분을 제거하는 방법 (SQL Server)?

---

내가 사용하는 것은 다음과 같습니다.

SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME)

더 좋고 우아한 방법이있을 수 있다고 생각합니다.

요구 사항 :

• 가능한 한 빨라야합니다 (주조 횟수가 적을수록 좋습니다).
• 최종 결과는 datetime문자열이 아닌 유형 이어야 합니다.

---

SQL Server 2008 이상

물론 SQL Server 2008 이상에서 가장 빠른 방법은 Convert(date, @date). 필요한 경우 datetime또는로 다시 캐스팅 할 수 있습니다 datetime2.

SQL Server 2005 및 이전 버전에서 가장 좋은 것은 무엇입니까?

SQL Server에서 날짜에서 시간을 줄이는 데 가장 빠른 것이 무엇인지에 대한 일관되지 않은 주장을 보았고 일부 사람들은 테스트를했다고 말했지만 제 경험은 달랐습니다. 그래서 좀 더 엄격한 테스트를하고 모든 사람이 스크립트를 갖게하여 내가 실수를하면 사람들이 나를 고칠 수 있도록합시다.

부동 변환이 정확하지 않음

첫째, 나는 변환을 멀리 할 datetime로 float제대로 변환하지 않기 때문에. 시간 제거 작업을 정확하게 수행하는 데는 벗어날 수 있지만 개발자에게 이것이 안전한 작업이고 그렇지 않다는 것을 암시 적으로 전달하기 때문에 사용하는 것이 좋지 않다고 생각합니다 . 구경하다:

declare @d datetime;
set @d = '2010-09-12 00:00:00.003';
select Convert(datetime, Convert(float, @d));
-- result: 2010-09-12 00:00:00.000 -- oops

이것은 우리의 코드 나 온라인 예제에서 사람들에게 가르쳐야하는 것이 아닙니다.

또한 가장 빠른 방법도 아닙니다!

증명 – 성능 테스트

여러 메서드가 실제로 어떻게 쌓이는 지 확인하기 위해 몇 가지 테스트를 직접 수행하려면 테스트를 더 아래로 실행하려면이 설정 스크립트가 필요합니다.

create table AllDay (Tm datetime NOT NULL CONSTRAINT PK_AllDay PRIMARY KEY CLUSTERED);
declare @d datetime;
set @d = DateDiff(Day, 0, GetDate());
insert AllDay select @d;
while @@ROWCOUNT != 0
   insert AllDay
   select * from (
      select Tm =
         DateAdd(ms, (select Max(DateDiff(ms, @d, Tm)) from AllDay) + 3, Tm)
      from AllDay
   ) X
   where Tm < DateAdd(Day, 1, @d);
exec sp_spaceused AllDay;  -- 25,920,000 rows

이렇게하면 데이터베이스에 427.57MB의 테이블이 생성되고 실행하는 데 15-30 분 정도 걸립니다. 데이터베이스가 작고 10 % 증가로 설정되어 있으면 먼저 충분히 큰 크기보다 더 오래 걸립니다.

이제 실제 성능 테스트 스크립트입니다. 행을 클라이언트로 다시 반환하지 않는 것은 2,600 만 행에서 엄청난 비용이 들고 메서드 간의 성능 차이를 숨길 수 있기 때문입니다.

성능 결과

set statistics time on;
-- (All queries are the same on io: logical reads 54712)
GO
declare
    @dd date,
    @d datetime,
    @di int,
    @df float,
    @dv varchar(10);

-- Round trip back to datetime
select @d = CONVERT(date, Tm) from AllDay; -- CPU time = 21234 ms,  elapsed time = 22301 ms.
select @d = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 23031 ms, elapsed = 24091 ms.
select @d = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23782 ms, elapsed = 24818 ms.
select @d = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 36891 ms, elapsed = 38414 ms.
select @d = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 102984 ms, elapsed = 109897 ms.
select @d = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 103390 ms,  elapsed = 108236 ms.
select @d = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 123375 ms, elapsed = 135179 ms.

-- Only to another type but not back
select @dd = Tm from AllDay; -- CPU time = 19891 ms,  elapsed time = 20937 ms.
select @di = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 21453 ms, elapsed = 23079 ms.
select @di = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23218 ms, elapsed = 24700 ms
select @df = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 29312 ms, elapsed = 31101 ms.
select @dv = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 64016 ms, elapsed = 67815 ms.
select @dv = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 64297 ms,  elapsed = 67987 ms.
select @dv = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 65609 ms, elapsed = 68173 ms.
GO
set statistics time off;

일부 램 블링 분석

이것에 대한 몇 가지 메모. 우선 GROUP BY 또는 비교 만 수행하는 경우 .NET Framework로 다시 변환 할 필요가 없습니다 datetime. 따라서 표시 목적으로 최종 값이 필요하지 않는 한이를 방지하여 일부 CPU를 절약 할 수 있습니다. 변환되지 않은 값으로 GROUP BY하고 변환을 SELECT 절에만 넣을 수도 있습니다.

select Convert(datetime, DateDiff(dd, 0, Tm))
from (select '2010-09-12 00:00:00.003') X (Tm)
group by DateDiff(dd, 0, Tm)

Also, see how the numeric conversions only take slightly more time to convert back to datetime, but the varchar conversion almost doubles? This reveals the portion of the CPU that is devoted to date calculation in the queries. There are parts of the CPU usage that don't involve date calculation, and this appears to be something close to 19875 ms in the above queries. Then the conversion takes some additional amount, so if there are two conversions, that amount is used up approximately twice.

More examination reveals that compared to Convert(, 112), the Convert(, 101) query has some additional CPU expense (since it uses a longer varchar?), because the second conversion back to date doesn't cost as much as the initial conversion to varchar, but with Convert(, 112) it is closer to the same 20000 ms CPU base cost.

Here are those calculations on the CPU time that I used for the above analysis:

     method   round  single   base
-----------  ------  ------  -----
       date   21324   19891  18458
        int   23031   21453  19875
   datediff   23782   23218  22654
      float   36891   29312  21733
varchar-112  102984   64016  25048
varchar-101  123375   65609   7843

• round is the CPU time for a round trip back to datetime.

• single is CPU time for a single conversion to the alternate data type (the one that has the side effect of removing the time portion).

• base is the calculation of subtracting from single the difference between the two invocations: single - (round - single). It's a ballpark figure that assumes the conversion to and from that data type and datetime is approximately the same in either direction. It appears this assumption is not perfect but is close because the values are all close to 20000 ms with only one exception.

One more interesting thing is that the base cost is nearly equal to the single Convert(date) method (which has to be almost 0 cost, as the server can internally extract the integer day portion right out of the first four bytes of the datetime data type).

Conclusion

So what it looks like is that the single-direction varchar conversion method takes about 1.8 μs and the single-direction DateDiff method takes about 0.18 μs. I'm basing this on the most conservative "base CPU" time in my testing of 18458 ms total for 25,920,000 rows, so 23218 ms / 25920000 = 0.18 μs. The apparent 10x improvement seems like a lot, but it is frankly pretty small until you are dealing with hundreds of thousands of rows (617k rows = 1 second savings).

Even given this small absolute improvement, in my opinion, the DateAdd method wins because it is the best combination of performance and clarity. The answer that requires a "magic number" of 0.50000004 is going to bite someone some day (five zeroes or six???), plus it's harder to understand.

Additional Notes

When I get some time I'm going to change 0.50000004 to '12:00:00.003' and see how it does. It is converted to the same datetime value and I find it much easier to remember.

For those interested, the above tests were run on a server where @@Version returns the following:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

---

SQL Server 2008 has a new date data type and this simplifies this problem to:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)

---

Itzik Ben-Gan in DATETIME Calculations, Part 1 (SQL Server Magazine, February 2007) shows three methods of performing such a conversion (slowest to fastest; the difference between second and third method is small):

SELECT CAST(CONVERT(char(8), GETDATE(), 112) AS datetime)

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

SELECT CAST(CAST(GETDATE() - 0.50000004 AS int) AS datetime)

Your technique (casting to float) is suggested by a reader in the April issue of the magazine. According to him, it has performance comparable to that of second technique presented above.

---

Your CAST-FLOOR-CAST already seems to be the optimum way, at least on MS SQL Server 2005.

Some other solutions I've seen have a string-conversion, like Select Convert(varchar(11), getdate(),101) in them, which is slower by a factor of 10.

---

Please try:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]

---

SQL2005: I recommend cast instead of dateadd. For example,

select cast(DATEDIFF(DAY, 0, datetimefield) as datetime)

averaged around 10% faster on my dataset, than

select DATEADD(DAY, DATEDIFF(DAY, 0, datetimefield), 0)

(and casting into smalldatetime was faster still)

참고URL : https://stackoverflow.com/questions/2775/how-to-remove-the-time-portion-of-a-datetime-value-sql-server