Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1

    Question Answered: SQL Query ORDER BY string time

    Hey all I have the following table that I am needing to order by depending on the time it starts.

    Example data:
    Code:
    4:30pm-10:00pm
    5:00am-4:00pm
    10:30am-4:00pm
    2:00pm-10:45pm
    5:00am-3:00pm
    3:30pm-10:00pm
    7:30am-3:30pm
    10:00am-3:30pm
    7:31am-1:01pm
    2:00pm-10:00pm
    So in the example string times above, I would need it to order them like so:
    Code:
    5:00am-3:00pm
    5:00am-4:00pm
    7:30am-3:30pm
    7:31am-1:01pm
    10:00am-3:30pm
    10:30am-4:00pm
    2:00pm-10:00pm
    2:00pm-10:45pm
    3:30pm-10:00pm
    4:30pm-10:00pm
    Currently I am getting the following when running this query:
    Code:
    SELECT [id]
          ,[mon]
      FROM [DBc83].[dbo].[tmpTable]
      ORDER BY mon DESC
    And the output is:
    Code:
        id  mon
        9   7:31am-1:01pm
        7   7:30am-3:30pm
        2   5:00am-4:00pm
        5   5:00am-3:00pm
        1   4:30pm-10:00pm
        6   3:30pm-10:00pm
        4   2:00pm-10:45pm
        10  2:00pm-10:00pm
        3   10:30am-4:00pm
        8   10:00am-3:30pm

  2. Best Answer
    Posted by StealthRT

    "This works the best:
    Code:
    SELECT [id],
           [mon]
    FROM   [DBc83].[dbo].[tmpTable]
    ORDER BY
           CAST(LEFT(mon, CHARINDEX('-', mon) -1) AS TIME),
           CAST(RIGHT(mon,LEN(mon)-(CHARINDEX('-',mon))) AS TIME)
    "


  3. #2
    Join Date
    May 2016
    Posts
    81
    Provided Answers: 3
    Hi

    So create this SQL query:

    Code:
    SELECT mon.mon, TimeValue(Left(mon,InStr(mon,'-')-1)) AS Expr1
    FROM mon
    order by TimeValue(Left(mon,InStr(mon,'-')-1))

  4. #3
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    This works the best:
    Code:
    SELECT [id],
           [mon]
    FROM   [DBc83].[dbo].[tmpTable]
    ORDER BY
           CAST(LEFT(mon, CHARINDEX('-', mon) -1) AS TIME),
           CAST(RIGHT(mon,LEN(mon)-(CHARINDEX('-',mon))) AS TIME)

  5. #4
    Join Date
    May 2016
    Posts
    81
    Provided Answers: 3
    I just give you the logic of the solution and you're right StealthRT, you give the T-SQL format solution !
    Last edited by informer; 05-31-16 at 10:00.

Posting Permissions

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