Post reply

Get date only and order by in correct way

  • July 15, 2013 at 9:43 am

    #279914

    I converted orderdate to get date only using code below.

    convert(varchar(20), orderdate, 101)

    The problem is that orderdate can not be sorted correctly. (because it is string now)

    For example, 05/20/2010 will list first when compare to 10/20/2005 (order by orderdate asc)

    How to solve this problem?

  • July 15, 2013 at 9:47 am

    #1632541

    Why won't you order by the original orderdate?

    In case you can't, try to convert it with a different format in the ORDER BY (such as 120 or 112).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 15, 2013 at 9:53 am

    #1632544

    Good idea to order by original column.

  • July 15, 2013 at 12:09 pm

    #1632625

    If the original data is a DATETIME (or other date datatype) there's no need to convert it to a varchar until after the sort. CAST the date value as DATE datatype if you are trying to get rid of the time part:

    DECLARE

    @testDate1 DATETIME

    ,@testDate2 DATETIME

    ,@shortDate1 DATE

    ,@shortDate2 DATE

    SET @testDate1 = '2013-07-15 13:58:37.583'

    SET @shortDate1 = CAST(@testDate1 AS DATE)

    SET @testDate2 = '2013-08-15 13:58:37.583'

    SET @shortDate2 = CAST(@testDate2 AS DATE)

    IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

    DROP TABLE #TempTable

    CREATE TABLE #TempTable (

    [ID] INT IDENTITY(1,1) NOT NULL,

    [testDate] DATE NULL,

    PRIMARY KEY (ID))

    INSERT INTO #TempTable

    SELECT @testDate1 UNION ALL

    SELECT @testDate2

    SELECT * FROM #TempTable

    ORDER BY testDate DESC

    --if you want to re-format, then do it in the select

    SELECT

    CONVERT(VARCHAR(20),testDate,111) AS testDate

    FROM #TempTable

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

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