0

I have start_date column in a table of type varchar.eg.,

GridSQL -> select start_date,count(*) from table_name group by start_date order by start_date asc;

  start_date
--------------         

  05-FEB-2010       

 11-FEB-2011         

 13-APR-2011          

 13-MAY-2011         

 14-APR-2011         

 14-MAY-2011         

 15-APR-2011         

 15-MAY-2011         

 16-JUN-2011       

 19-JUN-2011

description of table_name is..

GridSQL -> describe table_name;

   COLUMN_NAME      SQL_DATA_TYPE     TYPE_NAME      IS_NULLABLE  KEY 
----------------------------------------------------------------------
 start_date                  12      VARCHAR(1024)        YES     NO           
 A_partynumber               12      VARCHAR(100)         YES     NO

i need an query that works in GridSQL to convert the start_date into date format and sort as date wise.For that i used some queries but not worked.eg.,

GridSQL -> select to_date(start_date,'DD-MON-YYYY') from table_name group by to_date(start_date,'DD-MON-YYYY');

     to_date        
-----------------------

 2010-02-05 00:00:00.0 

 2011-06-19 00:00:00.0 

 2011-05-15 00:00:00.0 

 2011-06-16 00:00:00.0

 2011-04-13 00:00:00.0 

 2011-05-14 00:00:00.0 

 2011-05-13 00:00:00.0 

 2011-02-11 00:00:00.0 

 2011-04-15 00:00:00.0 

 2011-04-14 00:00:00.0

here date with time is coming and format of date is completely changed.i need date as 05-FEB-2010 and it is giving 2010-02-05 00:00:00.0

so what i am expecting is this

  start_date 
------------------------
 05-FEB-2010 

 11-FEB-2011

 13-APR-2011

 14-APR-2011

 15-APR-2011

 13-MAY-2011

 14-MAY-2011

 15-MAY-2011

 16-JUN-2011

 19-JUN-2011

is there any other options to try out.

thanks in advance

Nithesh Narayanan
  • 11,481
  • 34
  • 98
  • 138
Sukumar
  • 13
  • 3

1 Answers1

0

Try This:

select to_char(to_date(start_date,'DD-MON-YYYY'), 'DD-MON-YYYY')
from table_name
order by to_date(start_date,'DD-MON-YYYY') asc
Abhishek Jain
  • 2,597
  • 1
  • 18
  • 12
  • tried but not worked.giving SQLException: ERROR: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: column "table_name.start_date" must appear in the GROUP BY clause or be used in an aggregate function and i used group by also but same exception – Sukumar Jul 26 '13 at 09:32
  • thanks for ur response.its working.and i need to add group by clause also to the same query – Sukumar Jul 26 '13 at 09:47
  • Why do you need group by.. I don't think group by is required in the mentioned expected output. – Abhishek Jain Jul 26 '13 at 13:42