Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Unanswered: How to change date formats in stored procedure

    I need help on how to change the date format in a stored procedure. I am using the GetDate() function but need to convert it to short date format.

    thanks
    mike

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Look up CAST AND CONVERT in Books Online. But be aware that this changes the datatype to a string, and should be used for output formatting only. And it is preferable to let your interface or reporting tool handle formatting of output.
    Why do you think you need to convert it to short date? Are you trying to truncate the value?
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2005
    Posts
    4
    I am inserting a date value into a table and I dont want the timestamp portion included.

  4. #4
    Join Date
    Feb 2005
    Posts
    4

    Cool

    Thanks! I figured it out using the convert function

  5. #5
    Join Date
    Sep 2005
    Posts
    161
    A very similar question was answered yesterday.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Heck, cascred, this is one of those questions that gets asked every WEEK.

    musicmikem, this is a more efficient method of truncating a datatime value, if less intuitive: dateadd(d, datediff(d, 0, [YourDate]), 0)
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Weekly? Hell sometimes it's hourly
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    Weekly? Hell sometimes it's hourly
    Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by blindman
    Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.
    You make things so complicated. Why didn't you just say that today the question will be asked at:

    Code:
    create table Numlist (num int identity(1,1) not null primary key)
    go
    insert Numlist default values
    while scope_identity() < 24 insert numlist default values
    go
    select dateadd(hh, num, '10/4/2005') from numlist
    go
    drop table numlist
    Bill

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Because as any good DBA knows, that method requires a Brain Scan instead of a Clock Seek.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm actually in favor of the simpler:
    Code:
    SELECT DateAdd(hour, o0 + o1 * 8, dateadd(d, datediff(d, 0, GetDate()), 0))
       FROM (SELECT 0 AS o1 UNION SELECT 1 UNION SELECT 2) AS a
       CROSS JOIN (SELECT 0 AS o0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
       UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS b
    Bonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).

    -PatP

  12. #12
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by Pat Phelan
    Bonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).
    -PatP
    I like it. I have never seen this approach before.

    You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.

    Bill

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by cascred
    You used a base 8 system instead of base 10 since 8*3 = 24. Nifty.
    Gold star!

    Old Unix machines (especially the DEC ones) used to do nearly everything in octal. Three full octets (00-27 octal is 0-23 decimal) will exactly hold all of the hours in a day.

    -PatP

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by blindman
    Old people. Sheesh. Next you're going to ask if we want to see your hernia scar?
    Hey. Be careful what you suggest. Things weren't pretty in the days before a relational DBMS came along. We used to do this stuff in COBOL ... without SQL! There are some scars, but not from hernias.

Posting Permissions

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