0

Which of these two functions is more efficient? toDate1 or toDate2 -- Is there something better?

use Something
go
-- All SQL versions
create function toDate1(@t datetime)  
    returns datetime as begin  
    declare @return datetime  
    select  @return = dateadd(day,datediff(day,0,@t),0)
    return  @return end
go
-- SQL 2008 ->
create function toDate2(@t datetime)  
    returns datetime as begin  
    declare @return datetime  
    select  @return = convert(date, @t) 
    return  @return end
Sith2021
  • 3,245
  • 30
  • 22
  • 1
    This question appears to be off-topic because it belongs on [Code Review](http://codereview.stackexchange.com/) – DGibbs Jul 24 '14 at 15:02
  • I don't think you can really compare those two functions, one is for SQL Server 2008 and above and the other one is for All SQL Server versions. – Habib Jul 24 '14 at 15:03
  • Look at the execution plan and see for yourself – juergen d Jul 24 '14 at 15:03
  • 1
    Why do you need a function for this? [CONVERT](http://msdn.microsoft.com/en-us/library/ms187928.aspx) will do it for you. – Andrew Jul 24 '14 at 15:03
  • When you tested it what were your results? – Conrad Frix Jul 24 '14 at 15:06
  • Neither of them is terribly efficient. They are both scalar functions which are notoriously slow in all versions of sql server. – Sean Lange Jul 24 '14 at 15:11
  • An application will read some databases that was created with SQL 5005, but the new comes in SQL 2012 and 2014. therefore I need a common function in both versions. I need the best performance because the queries works over millions of records. I wanted to spare me some time writing something for assessing performance. So the question. – Sith2021 Jul 24 '14 at 16:24
  • Sean, shall you say me a better approach? – Sith2021 Jul 24 '14 at 16:45
  • for sql-server 2008+ if you just want to return the date from a datetime, you can use cast(mydatetimevar as date) – Kevin Cook Jul 24 '14 at 20:10

1 Answers1

0
SELECT convert(date, cast ('2014-01-01 10:11:12' as datetime), 106)

it return date '2014-01-01' cast ('2014-01-01 10:11:12' as datetime) is a datetime variable. Please look msdn for more detail

tuan huynh
  • 727
  • 5
  • 13