Results 1 to 5 of 5

Thread: SQL Time format

  1. #1
    Join Date
    Sep 2006
    Posts
    13

    Question Unanswered: SQL Time format

    Hello,

    I am taking datetime value from a table MA and separating date and time by using CONVERT function.

    CONVERT(VARCHAR(12), MA.ArrivalDate, 101) AS ArrivalDate, '
    CONVERT(VARCHAR(12), MA.ArrivalDate, 108) AS ArrivalTime,

    but my ArrivalTime is coming as 13:01:00. I want to convert this time as 1:01:00. I have to do this conversion in my stored proc, not in my code. can you help ..? Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why would you want to convert perfectly useful datetime values to crappy strings? Arguably the most common database application development mistake...
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    have the app developer handle this at the presentation layer.
    “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.

  4. #4
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    Quote Originally Posted by neha101
    Hello,

    I am taking datetime value from a table MA and separating date and time by using CONVERT function.

    CONVERT(VARCHAR(12), MA.ArrivalDate, 101) AS ArrivalDate, '
    CONVERT(VARCHAR(12), MA.ArrivalDate, 108) AS ArrivalTime,

    but my ArrivalTime is coming as 13:01:00. I want to convert this time as 1:01:00. I have to do this conversion in my stored proc, not in my code. can you help ..? Thanks
    How about the AM / PM ?

    you can use other style with hh:mi:ss:mmmAM and use substring to extract the required portion
    -----------------
    KH


  5. #5
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    If you are going to work with large amount of data at a time than may be concerning performance it will not so efficient.

    SELECT SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 100), 13, 7) AS ArrivalTime

    or

    SELECT SUBSTRING(CONVERT(VARCHAR(26), GETDATE(), 109), 13, 17) AS ArrivalTime
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

Posting Permissions

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