Date with No time...
-
Is there an easy way to take a datetime and return just the date? My problem would be better said subtract the time portion. Actually neither of those make sense. Here's an example...
I have 8/20/2005 12:24:12.345
I want 8/20/2005 00:00:00.000
Basically strip off the time. I can do dateadds for each of the time components. Or I can rebuild the date off of the month year and day of the date passed in. Both get kind of messy kind of quickly. Is there an easier/cleaner way to do this?
Hmm... Maybe dateadds w/ the month, day and year will work. I may have answered my own question. Any other suggestions?
-
Would CONVERT(datetime, CONVERT(varchar, [datefield], 101)) work?
Haven't tried it but just a thought... because it strips out time references and into mm/dd/yyyy format then back to datetime, theoretically it should default to 00:00:00.000 for the time component.
It is the end of my working day though...
-
That'll do it. And much more cleanly than anything I would have come up with. Or at least much less code...
Muchos gracias, Señor!
-
No problem... glad to see the 12 mugs of coffee today have paid off and kept me awake!
-
Might I suggest this version as it is math based instead of converts?
If you have a big resultset to convert, you'll see a difference.
dateadd(d, 0, datediff(d, 0, getdate()))
-
That works, too. And its 11 characters shorter! Don't have that large of a resultset, so I don't see a performance difference (at least right now... this function I'm writing will eventually get used over a much larger resultset).
But its 11 characters shorter! Less is MORE!
Just kidding. Thanks for the help! -
This version IS FASTER. Not by a lot, but enough to consider it on a system at least as a best pratice. Instead of converting from datetime to varchar(working to trim the time) and back to datetime, the cpu simply has to do this :
dateadd(d, 0, datediff(d, 0, getdate()))
=
0 + int(GetDate() - 0) and the job is done
. Now you know why it's faster than converting twice. -
This is the fastest way i have found
(CAST(CAST(GetDate() as FLOAT) AS INTEGER) AS DATETIME)
if you want to do calculations or find things in the same day you dont need to convert to datetime
CAST(GetDate() as FLOAT) AS INTEGER
each day will have a unique int.
-
I have also used John Franco's method - it is worth pointing out though that you cannot simplify this to
select cast(cast(getdate() as integer) as datetime)
even though it might seem logical to do this - because if you do when you get through to float valus > 0.5 it will round up to the next int value and thus the next date.
To make this more obvious to others I slightly prefer
select cast(floor(cast(getdate() as float)) as datetime)
to the
select cast(cast(cast(getdate() as float) as integer) as datetime)
as then it is clear that the float value is being truncated
James Horsley
Workflow Consulting Limited -
The whole point of this is to allow me to do aggregates based on a shift. Our shifts run 8 to 8 (ie, 8AM to 8PM to 8AM) and our week starts/ends on 8AM Sunday.
If anyone's interested, here are the full details of what I'm doing. I'm writing a function that will return a datetime that represents the start of that shift. I'm then using that to group on for sum/avg etc.
Here are some examples of what the function should return....
8/30/2005 14:05:00 returns 8/30/2005 08:00:00
8/30/2005 07:05:00 returns 8/29/2005 20:00:00
8/30/2005 22:05:00 returns 8/30/2005 20:00:00
The function as written (which works, but could probably be better) is ...
create function StartOfShift
(@Date datetime )
returns datetime
as
begin
declare @return as datetime
declare @hour as integer
set @hour = datepart(hh, @Date)
set @return = CONVERT(datetime, CONVERT(varchar, @date, 101))
if @hour = 20
set @return = dateadd(hh, 20, @return)
else
set @return = dateadd(hh, 8, @return)
return @return
end
-
I just played with this... looks like we are both right about what is he fastest... just depends on the number of lines :
Declare @i as int
Declare @Maxi as int
Declare @LastRun as int
Declare @Runs as tinyint
Declare @MaxRuns as tinyint
Declare @MaxLoops as int
Declare @StartT as datetime
Declare @Time1 as int
Declare @Time2 as int
Declare @Temp as datetime
Declare @Results table (Loops int not null, RunNbr tinyint, Maths smallint not null, Converts smallint not null, primary key clustered (Loops, RunNbr, Maths, Converts))
Declare @Loops table (Loops int not null primary key clustered)
INSERT INTO @Loops (Loops)
Select 100 UNION ALL
Select 250 UNION ALL
Select 500 UNION ALL
Select 1000 UNION ALL
Select 5000 UNION ALL
Select 10000 UNION ALL
Select 50000
--set the limit of loops for this test
SET @MaxLoops = 10000
--compare
--Select cast(floor(cast(getdate() as float)) as datetime) as converts, dateadd(d, 0, datediff(d, 0, getdate())) as Maths
--start the Runs loop
SET @MaxRuns = 11
SET @Runs = 1
WHILE @Runs @Maxi and Loops <= @MaxLoops Order by Loops
WHILE @Maxi IS NOT NULL
BEGIN
SET @i = 0
SET @StartT = GetDate()
WHILE @i < @Maxi
BEGIN
SET @Temp = dateadd(d, 0, datediff(d, 0, getdate()))
SET @i = @i + 1
END
SET @Time1 = datediff(ms, @StartT, GetDate())
SET @i = 0
SET @StartT = GetDate()
WHILE @i @LastRun and L.Loops <= @MaxLoops Order by L.Loops
END
SET @Runs = @Runs + 1
END
Select Loops, RunNbr, Maths, Converts, CAST(100.0 * Converts / CASE Maths WHEN 0 THEN 1 ELSE Maths END -100 AS Decimal (18,2)) AS PercentFaster from @Results order by Loops, RunNbr
-
What I found out when testing performance is that the amount of time spent in the TSQL interpreted code takes all the time. I tested against a table with 86,000 rows that had a date field.
select CAST(CAST(CAST(jIn AS FLOAT) AS INTEGER) AS DATETIME) as foo from envelopereport
select CAST(FLOOR(CAST(jIn AS FLOAT)) AS DATETIME) as foo from envelopereport
select dateadd(d, 0, datediff(d, 0, jIn)) as foo from envelopereport
select CAST(CAST(jIn AS FLOAT) AS INTEGER) as foo from envelopereport
And looked at the CPU time. Thats when I could see that the cast method is slightly faster than the dateadd method and 4x faster than the best convert method.
-
So true, thanx for the new performance tuning insigth
. -
The easiest way is to use an ODBC canonical function which SQL Server supports.
select {fn CURRENT_DATE()} AS 'date only'
See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcscalar_functions.asp for more information on ODBC functions.
-
And another perf comparison...
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=139939&p=2
Moral: no strings!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply