Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-10 : 09:28:24
|
| Greg writes "I need to be able to truncate a datetime field to its date, setting the time to be 12:00:00AM, so that all records during the same day will have the same datetime value.In Oracle, you can actually use trunc(somedate). Any ideas?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-10 : 09:38:03
|
| Use the CONVERT() function:SELECT Convert(datetime, Convert(char(8), dateColumn, 112))FROM myTable |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-06-10 : 12:35:21
|
| Though it takes more keystrokes, it's a good deal faster to use DATEDIFF and DATEADD.DATEADD(day, DATEDIFF(day, '1900-01-01', dateColumn), '1900-01-01')where 1900-01-01 is arbitrary. Obviously the query plan and cost estimations are the same, but scalar operations aren't counted there.For example, I have a table containing ~2 million rows of dates (and nothing else) on 1452 distinct days.SELECT DATEADD(day, DATEDIFF(day, '1900-01-01', dateColumn), '1900-01-01'), COUNT(*)FROM TestdateGROUP BY DATEADD(day, DATEDIFF(day, '1900-01-01', dateColumn), '1900-01-01')SQL Server Execution Times: CPU time = 5906 ms, elapsed time = 3048 ms.SELECT Convert(datetime, Convert(char(8), dateColumn, 112)), COUNT(*)FROM TestdateGROUP BY Convert(datetime, Convert(char(8), dateColumn, 112))SQL Server Execution Times: CPU time = 39265 ms, elapsed time = 19787 ms.(2 PIII 1.13MHz processor server, SQL Server 2000 sp 2) |
|
|
|
dsdeming
479 Posts |
Posted - 2002-06-10 : 13:42:08
|
| Nice solution Arnold. I tried it on a smaller set ( approximately 100K rows ) and your solution used roughly 20% of the cpu time and took 50% as long as the CONVERT method.One oddity though: I initially tried it on a table with 75K null dates, and CONVERT was actually marginally faster. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-10 : 14:25:25
|
| Probably because DateAdd and DateDiff each will still compare a Null to a date value, while the Convert function(s) will just push the null through right away. |
|
|
|
|
|
|
|
|
Truncate Date