how to sort the date ?

selva kumar

13y1.7k

here is my sample code

create table [sample](T_id int identity(1,1),date varchar(50));

04-05-2013
07-03-2013
10-01-2013
20-08-2013
18-02-2013
09-07-2013
07-06-2013
05-12-2013
05-12-2013
05-12-2013

i want to sort the date based on DD-MM-YYYY fomat:

i m using below query

select distinct convert(varchar,date,103),convert(datetime,date,103)as date from [sample] order by date desc;

i m getting below output,

[no column name]       date
05-12-2013        2013-12-05 00:00:00.000
20-08-2013         2013-08-20 00:00:00.000
09-07-2013       2013-07-09 00:00:00.000
07-06-2013         2013-06-07 00:00:00.000
04-05-2013         2013-05-04 00:00:00.000
07-03-2013         2013-03-07 00:00:00.000
18-02-2013         2013-02-18 00:00:00.000
10-01-2013         2013-01-10 00:00:00.000

how do i get the column name for fist one...? can anyone help my question..

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

selva kumar13y ago
it displays YYYY-MM-DD format...but this not my solution....

i want to display DD-MM-YYYY... tell me another way......
Hari Bharadwaj Vangala13y ago
http://stackoverflow.com/questions/15194968/sql-sort-month-ascending-and-months-descending

You should declare ur date1 also as datetime then u can use the feature as present in above link...
If u declare ur date 1 as datetime then u get the above expected result...

select date1 from
(select distinct convert(datetime,date,103) as date1,convert(datetime,date,103)as date2 from [sample])t
order by MONTH(date1) desc

Please convert the o/p in the design page while u display.Hope this helps....refining my knowledge:P...tq.
selva kumar13y ago
ur query gives following output:

20-08-2013
18-02-2013
10-01-2013
09-07-2013
07-06-2013
07-03-2013
05-12-2013
04-05-2013

but i am expecting like this; sorting based on month vice....

05-12-2013
20-08-2013
09-07-2013
07-06-2013
04-05-2013
07-03-2013
18-02-2013
10-01-2013

tell me another way.....
Pankaj Pandey13y ago
select date1 from (select distinct convert(varchar,date,103) as date1,convert(datetime,date,103)as date2 from [sample])
order by date1 desc;
Hari Bharadwaj Vangala13y ago
select date1 from (select distinct convert(varchar,date,103) as date1,convert(datetime,date,103)as date2 from [sample]) t
order by date1 desc;

This will work perfectly...
selva kumar13y ago

select distinct convert(varchar,date,103)as date,convert(datetime,date,103)as date1 from [sample] order by date desc;

this is my query. if i m using this...

i m getting two columns.....like this...

date1                               date
05-12-2013        2013-12-05 00:00:00.000
20-08-2013         2013-08-20 00:00:00.000
09-07-2013       2013-07-09 00:00:00.000
07-06-2013         2013-06-07 00:00:00.000
04-05-2013         2013-05-04 00:00:00.000
07-03-2013         2013-03-07 00:00:00.000
18-02-2013         2013-02-18 00:00:00.000
10-01-2013         2013-01-10 00:00:00.000

i want to display first column........
Pankaj Pandey13y ago

hiiii,

select convert(varchar,getdate(),103) as Date_varchar,convert(datetime,getdate(),103)as Date_datefrom [sample] order by date desc;

mark as answered if helpful
selva kumar13y ago
i get the column name for first one ...then i want to display the first column results....
Hari Bharadwaj Vangala13y ago
select convert(varchar,getdate(),103) as date1,convert(datetime,getdate(),103)as date from [sample] order by date desc;

Use the same "as" for first column, like u used in second. Hope this was expected answer.

Hope this helps..