How to sort Varchar Date in SQL Server 2000

My table schema (table Name Stock)

Field

Date Datetime

If Date having datatype is Datetime then SQL Server sorts the date in well/perfect order. See Below Example.

select date from stock order by date

Then result is:

Date(yyyy/MM/dd)
2010-04-02 00:00:00.000
2011-02-02 00:00:00.000
2011-02-03 00:00:00.000
2011-02-03 00:00:00.000

But if I am going to sort the same by casting varchar than it will be ultimately create problem and the problem is it will not sort date by date order as it is now casting in Varchar.

Look at example below:

select date = convert(varchar, date, 103) from stock order by date

Then result is:

Date(dd/MM/yyyy)
02/02/2011
02/04/2010
03/02/2011
03/02/2011

You can see second query’s result that it will not sort date in order as because it is now not in datetime datatype. It is casting as varchar or you can say it as string.

Now come to the point that:

If I want to sort date which is casting in varchar order by date than how to do it?.

Answer

What you’re doing is converting a varchar to a varchar and then sorting by that… you’re not sorting by date!

convert(varchar, date, 103)
       *********

This type here is what you convert your value into – you’re converting into a varchar – of course it won’t sort by date then!

Try this instead:

SELECT CONVERT(DATETIME, date, 103) 
FROM dbo.Stock 
ORDER BY CONVERT(DATETIME, date, 103) 

NOW you’re actually converting your varchar column date to a DATETIME value, and sorting on that resulting DATETIME – and now you get the output:

2010-04-02 00:00:00.000
2011-02-02 00:00:00.000
2011-02-03 00:00:00.000
2011-02-03 00:00:00.000

Update: if you need another formatting, you can of course convert your DATETIME again, back into a VARCHAR using CONVERT with a different style:

Read all about what styles are supported in MSDN CAST and CONVERT

SELECT 
    date = CONVERT(VARCHAR, CONVERT(DATETIME, date, 103), 103)  -- convert back to VARCHAR
FROM 
    dbo.Stock 
ORDER BY 
    CONVERT(DATETIME, date, 103)   -- sort by the DATETIME !

and then you get this output:

02/04/2010
02/02/2011
03/02/2011
03/02/2011

Just make sure to sort on the DATETIME value! (not on a string representation of your DATETIME)

And as I said: if you store your dates as DATETIME from the beginning, you’ll save yourself a lot of those conversion back and forth!!