advanced code snippet search

Posted By

hoffstein on 10/14/08


Tagged

tsql


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

hoffstein
JerryNixon


TSQL truncate time

 / Published in: SQL
 

Returns the date associated with a given datetime value (@date).

Expand | Embed | Plain Text 
  1. DATEADD(d, DATEDIFF(d, 0, @date), 0)

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: orip on August 5, 2009

Awesome, thanks!

Posted By: JerryNixon on November 19, 2009

I have tested and your technique is the fastest option, even with the Date data type in SQL 2008.

Try this:


DECLARE @Table TABLE
   (
     Date1 datetime
     ,Date2 datetime
     ,Date3 date
   )

DECLARE @i int
SET @i = 0
WHILE (@i < 1000000)
    BEGIN
        INSERT INTO @Table
        SELECT GetDate() + @i, null, null
        SET @i = @i + 1
    END
    
DECLARE @Timer datetime

SET @Timer = getdate()
UPDATE @Table SET Date2 = Date1, Date3 = Date1
PRINT 'Warm Up'
PRINT DateDiff(ms, @Timer, getdate())

SET @Timer = getdate()
update @Table Set Date2 = CONVERT(DATETIME, SUBSTRING(CONVERT(varchar, Date1), 1, 11))
PRINT '(English Culture only) Parse with SubString to DateTime'
PRINT DateDiff(ms, @Timer, getdate())

SET @Timer = getdate()
update @Table Set Date2 = CONVERT(varchar, Date1, 101)
PRINT 'Convert to Varchar 101 to DateTime'
PRINT DateDiff(ms, @Timer, getdate())

SET @Timer = getdate()
update @Table Set Date2 = CONVERT(DATE, Date1)
PRINT '(SQL2008 only) Convert from DateTime to Date to Datetime'
PRINT DateDiff(ms, @Timer, getdate())

SET @Timer = getdate()
update @Table Set Date3 = Date1
PRINT '(SQL2008 only) Convert from DateTime to Date'
PRINT DateDiff(ms, @Timer, getdate())

SET @Timer = getdate()
update @Table Set Date2 = DATEADD(d, DATEDIFF(d, 0, Date1), 0)
PRINT 'Use DateDiff then DateAdd'
PRINT DateDiff(ms, @Timer, getdate())

You need to login to post a comment.