Post reply

DateTime without hour

  • December 22, 2005 at 2:22 am

    #89089

    Hello,

    I have a smalldatetime with date and hour, i want to transform this date to have the hour, min , sec to zero

    2005-12-22 14:22 --> 2005-12-22 00:00

    For the moment i convert my date in string and i reconverte in datetime, do you know if there a better way ?

    Thanks

    Sylvain

  • December 22, 2005 at 2:36 am

    #611054

    Try this trick (which I have learned here at SSC ):

    select dateadd(d, datediff(d, '1900', '2005-12-22 14:22'), '1900')

  • December 22, 2005 at 2:57 am

    #611059

    Or my personal favorite way

    DECLARE @dt DATETIME

    SET @dt = '2005-12-22 14:22'

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

    Takes advantage of the fact that a date is stored like a float, with the time in the fraction.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 22, 2005 at 3:28 am

    #611065

    Why not

    SELECT CAST(CAST(GETDATE() AS INT) AS DATETIME)

    LinkedIn Profile

  • December 22, 2005 at 4:14 am

    #611070

    umm.. not to be picky, but it's not stored as a float, it's stored as two two-byte ints..

    /Kenneth

  • December 22, 2005 at 5:23 am

    #611078

    Picky, picky. Close enough. The second 2 ints are treated like a fractional value (select getdate()+0.25)

    How is a float stored? (

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 22, 2005 at 5:24 am

    #611080

    Becaust I want to be explicit about round off vs truncate fractions.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 22, 2005 at 5:50 am

    #611083

    Thanks everobody

  • December 22, 2005 at 6:03 am

    #611086

    heh not going there. I'm sure you're right about that.

    But, since it's x-mas, I'll just mess you all up with my favorite version

    DECLARE @dt DATETIME

    SET @dt = '2005-12-22 14:22'

    SELECT CAST(CONVERT(CHAR(8), @dt, 112) AS DATETIME)

    /Kenneth

  • December 22, 2005 at 6:17 am

    #611089

    So many ways to skin a cat.

    How about one of my least favorite ways. (Found this in my production system when I was investigating why certain queries were using way more CPU than expected.)

    DECLARE @dt DATETIME

    SET @dt = '2005-12-22 14:22'

    SELECT CAST(CAST(DATEPART(YY,@dt ) as varchar(10)) + '/' +

     RIGHT('00' + CAST(DATEPART(MM,@dt ) as varchar(10)),2) + '/' +

     RIGHT('00' + CAST(DATEPART(DD,@dt ) as varchar(10)),2) AS DATETIME)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply