Results 1 to 10 of 10

Thread: truncate the time portion in datetime stamp

  1. #1
    Join Date
    Sep 2002
    Posts
    78

    truncate the time portion in datetime stamp

    Hi all:
    first of all, i must say that this website is just awesome...

    my question is how do i truncate the time portion in a datetime stamp in a single sql statement.

    thanks.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Can use datepart function, check books online for details.

  3. #3
    Join Date
    Sep 2002
    Posts
    78

    datepart function

    Yes, currently i am using the datepart function to get what I need..I was just wondering if there was a different way to do it.

    Thanks

  4. #4
    Join Date
    Sep 2002
    Posts
    169
    Have a look at functions CONVERT and CAST.

    e.g. SELECT CONVERT (VARCHAR, GETDATE(), 112)

    will give you something like 20030402

    Check the other "style" values in books on line to see which is the closest match to your desired result

  5. #5
    Join Date
    Apr 2003
    Posts
    1
    There's also
    SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))

    which will also get the job done, and seems to be somewhat marginally faster. Works through side effects of data conversion, however...

  6. #6
    Join Date
    Dec 2002
    Posts
    1
    Originally posted by Wobin
    There's also
    SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))

    select dateadd(dd, datediff(dd, 0, getdate()), 0)

    This should be fast and more accurate than the float conversion.

    Bambola.

  7. #7
    Join Date
    Oct 2003
    Posts
    2
    I just had to research this myself as well.

    A datetime is essentially the same as a float.
    So in a better world
    select floor(getdate())
    would truncate to midnight.

    However, MS in their infinite wisdom does not allow implicit translation between datetime and float.
    So you have to convert it, then floor it, then convert it back to a datetime
    hence
    SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))

    These all do the same thing
    convert(datetime,convert(int,getdate()-0.5)),
    convert(datetime,floor(convert(float,getdate()))),
    cast(cast(getdate()-0.5 as int) as datetime),
    cast(floor(cast(getdate() as float)) as datetime)

    This isn't a 'side effect' of convert(), it's just manipulation of numbers.
    Using the datediff and dateadd method is not more accurate or faster, but some may be more comfortable with it.

    I ran some tests and
    cast(cast(getdate()-0.5 as int) as datetime)
    or
    convert(datetime,convert(int,getdate()-0.5))
    are 5% faster than using floor()
    Using datediff and dateadd takes exactly the same time as the floor() method.

    My 6 bits worth.

    Ike

  8. #8
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    select left(getdate(),11)

  9. #9
    Join Date
    Oct 2003
    Posts
    2
    that returns a varchar, not a datetime

  10. #10
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    select convert(datetime,left(getdate(),11))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •