Results 1 to 7 of 7

Thread: getdate()

  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: getdate()

    I'm using getdate() - 74 and getting something like

    2006-03-06 11:26:02.870

    Is there a simple way to get

    2006-03-06 00:00:00.000

    instead?

    Or just get the date and not the time part. I'm using this date with a between function and the time component is throwing it off.

    I'm using

    cast(convert(char, getdate(),112) as datetime) - 74

    and it works...but there must be an easier way...
    Thanks,
    Bill

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dateadd(d,datediff(d,0,getdate())-74,0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not really any easier way. You can make this a user defined function, to hide the complexity, though. Maybe create a "today" function that strips the time off getdate().
    Code:
    create function today (@date datetime)
    returns datetime
    as
    begin
    return convert(datetime, convert (int, @date))
    
    end
    go
    
    select dbo.today (getdate())
    SNIPED! and with a much better solution, too.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, the original solution is blindman's, who determined that arithmetic on integers is way more efficient than converting the date (which, as you know, is stored as an integer) to a character string, truncating the time characters, and converting back

    applying the formula by sticking -74 into the appropriate place, though, that was me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    well, the original solution is blindman's, who determined that arithmetic on integers is way more efficient than converting the date (which, as you know, is stored as an integer) to a character string, truncating the time characters, and converting back

    applying the formula by sticking -74 into the appropriate place, though, that was me
    Wish I could claim credit for that, but the best I can say is that I adopted it immediately when I saw it posted by somebody else on this forum.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wait a sec, that somebody else might have been me!!

    and i sure as shootin' didn't come up with it myself, i think i got it from another forum --

    http://www.tek-tips.com/faqs.cfm?fid=5842 tip #7

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

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    wait a sec, that somebody else might have been me!!
    Maybe it WAS you instead of me. And hey....what the?...how did I end up wearing these lederhosen? OK, this is freakin' me out.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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