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

  • 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://www.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