Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    32

    Unanswered: Strip off hour, minute, second from a datetime?

    I would like something I can do inline eg:

    select convert(blahdatatype,a.datefield) as smallerdatefield
    from
    a

    where a.datefield is a datetime. If a contains rows like:

    datefield
    ---------------------
    01/20/2005 22:17:23
    08/23/2001 03:04:15
    ...

    Then the SQL above returns:

    smallerdatefield
    ---------------------
    01/20/2005 00:00:00
    08/23/2001 00:00:00
    ...

    Is there any non-obnoxious way (eg: without have to result to using datepart a million times) to do this? For instance, Oracle provides a function called Trunc which does it, but I cannot find an SQL Server equivalent. Anyone? TIA!!!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I was going to have you do this:

    SELECT LEFT(CAST(getdate() as varchar),11)

    but it kicks out this:

    Jan 18 2005

    I hate computers
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    CONVERT(char(10), GetDate(), 120)

    ...returns a string representing the date part of the value, which SQL Server can implicitly convert back to a date value.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    Would this suit your needs?

    select convert(varchar(10), MyDateTime, 102)
    from Foo
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    gentlemen, please, the result should be a datetime, not a string

    select dateadd(d,datediff(d,0,yourdate),0) as smallerdate ...

    some guys on another forum (link on request) actually ran some cpu tests on it, and this is by far the fastest method
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I can't tell if you are being seriouse or not, Rudy...confound your Canadian sense of humor, eh?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    serious

    http://www.tek-tips.com/viewthread.cfm?qid=972119

    actually, i half expected you to come up with something similar, based on your previous expertise with date calculations -- http://r937.com/birthdays.html

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Interesting and valuable, though not that surprising. I thought the two numeric functions might be faster than a single string function, if a little more obscure. Good to know.

    But who the heck is this Donutman that thinks triggers and user-defined functions are evil? He calls himself a programmer?

    Respect
    ...dropping
    ......to
    .........zero.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2005
    Posts
    10
    Why not just set the dateformat of your datetime output to small date?

    In that sense, you dont have to keep on truncating the time.

    You can do that using SET.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    manilaguy, could you please explain? maybe with an example?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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