Post reply

ORDER BY datetime help

  • September 9, 2010 at 12:47 pm

    #226179

    I have a strange issue here.

    If I run the following SQL query in SQL management studio, it works fine, lists all the correct data, no errors, etc.

    SELECT CONVERT(VARCHAR, StartTime,108) AS justTime

    FROM theList

    ORDER by StartTime

    However I use the exact same query, but call it from an ASP page, I get the error "Conversion failed when converting date and/or time from character string."

    The only way to get rid of the error that I've found so far, is to write the query like this:

    SELECT CONVERT(VARCHAR, StartTime,108) AS justTime

    FROM theList

    ORDER BY CAST(StartTime AS VARCHAR(50))

    However, this messes up the ORDER BY...essentially it no long orders by the time correctly.

    Is there anyway around this?

    Thanks!

  • September 9, 2010 at 1:04 pm

    #1218863

    Your cast statement in the WHERE clause will return a format like 'Sep 9 2010 8:59PM'.

    Hence the messed up order.

    Did you try using the same CONVERT statement in the WHERE clause that you have in your SELECT statement?

    SELECT CONVERT(VARCHAR, StartTime,108) AS justTime

    FROM theList

    ORDER BY CONVERT(VARCHAR, StartTime,108)

    -- alternatively, if you want to sort it by date and time: CONVERT(VARCHAR, StartTime,121)


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 10, 2010 at 3:09 am

    #1219046

    Can you confirm the data type of the column startTime please? - Best to post th eentire crate table statement.

    Also is the asp page connected to the same database - with the same data in it?

    Mike

  • September 10, 2010 at 7:45 am

    #1219155

    thanks! That worked!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply