Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Truncating Date Expand / Collapse
Author
Message
Posted Wednesday, April 12, 2006 5:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 25, 2012 8:03 AM
Points: 17, Visits: 9

What is the best/most efficient way to truncate DateTime to it's Date value only?

I have a stored procedure that is receiving DateTime and I only want to insert Date part of it (truncate time to 0:00:00)

I don't see any function for this and the only solution I could come up with is to string concatanation with year,month,day function and cast to date back, but that seems way too unefficient.

Thanks.

Post #272895
Posted Wednesday, April 12, 2006 5:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 25, 2012 8:03 AM
Points: 17, Visits: 9

I found a solution on google:

SELECT DATEADD(dd, DATEDIFF(dd,0,@x), 0)

but that seems odd to do datediff and dateadd to get the result.
I wish there was a built in function for that.

Post #272899
Posted Wednesday, April 12, 2006 5:38 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:24 PM
Points: 2,835, Visits: 1,124

The solution you have is reasonable, but there are many variations.  I usually cast the date to a float, truncate it, and cast back to a date.

SELECT CAST(FLOOR(CAST(@x AS FLOAT)) AS DATETIME)

You would probably have to execute this conversion many times to see a performance difference, if any.

PS You can't use CAST(CAST(@x AS INT) AS DATETIME) because it will round PM times up to the next date.




Post #272902
Posted Wednesday, April 12, 2006 5:38 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 31, 2014 10:05 AM
Points: 2,553, Visits: 559
use convert and apply a style to get only the date...

select convert(varchar, getdate(), 101)

there're any # of formats that you can apply - check BOL for the one that fits your need best!








**ASCII stupid question, get a stupid ANSI !!!**
Post #272903
Posted Wednesday, April 12, 2006 6:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 25, 2012 8:03 AM
Points: 17, Visits: 9
I would think this would be slower than DateAdd solution as this converts to varchar and back.
Post #272911
Posted Wednesday, April 12, 2006 11:27 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315

Absolutely!

Don't convert datetime to varchar, unless you need result as string for reporting purposes.

Post #272933
Posted Thursday, April 13, 2006 4:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 01, 2013 1:55 PM
Points: 123, Visits: 104
Converting from datetime to varchar and back would be an awful thing to do for looking at a million rows, but performed once per call to modify a stored procedure parameter will not measurably affect performance (unless you are calling the procedure a million times, and then you have bigger problems
Post #272973
Posted Thursday, April 13, 2006 5:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 22, 2009 5:13 AM
Points: 149, Visits: 11
Just out of curiosity, WHY do you not want to store the time? I mean, storing a 0 time doesn't save you any space. It's easy enough to use the DATEPART functions when comparing the values, to ignore the time portion. I might see some advantage to indexing, but that's about it.
Post #273000
Posted Thursday, April 13, 2006 6:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 09, 2011 7:49 AM
Points: 343, Visits: 188
This comes up regularly. Ive tested most scenarios in the past and datediff/dateadd always comes out the fastest - often by a good margin. It also has the advantage of being locale-independent (doesnt matter whether your dates are ddmmyyy or mmddyyyy or whatever).


Post #273012
Posted Thursday, April 13, 2006 6:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 10:00 AM
Points: 313, Visits: 1,549

Here  a script you can use to test the 3 methods.

There is a measureable difference between the convert to varchar and the other 2 options.

Only when you increase the number of iterations > 30,000 do you start to see the speed improvement of the last one

(dateadd(datediff)) but as others have said for a small number of conversions any will work - as long as you take into account the locale specific needs for the varchar option.

Kevin

*****************************

declare @loop int, @d datetime
declare @lMax int
set @lMax = 3000
declare @dStart datetime, @dEnd datetime

--various methods to strip off the time portion off a date

set nocount on
set @loop = 1
set @dStart = getdate()
--uses convert
while @loop <@lMax
begin
 set @d= CONVERT(datetime, CONVERT(varchar, getdate(), 101))
 set @loop = @loop + 1
end
select datediff(ms, @dStart,getdate())


set @loop = 1
set @dStart = getdate()
--uses cast
while @loop <@lMax
begin
  set @d = cast(floor(cast(getdate() as float)) as datetime)
 set @loop = @loop + 1
end
select datediff(ms, @dStart,getdate())

set @loop = 1
set @dStart = getdate()
--uses datediff, dateadd
while @loop <@lMax
begin
 set @d= dateadd(d, 0, datediff(d, 0, getdate()))
 set @loop = @loop + 1
end
select datediff(ms, @dStart,getdate())

set nocount off

 




Post #273027
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse