CODEWITHSUNDEEP
sqloracle-database
hudi
hudi

Reputation: 16555

select and order date from oracle database

I have date in database in this format 04-03-12 23:00:00. How I can convert it to this format 16.02.2012 and still user order by ? Because when I used to_char then order by doesnt work correctly

Upvotes: 1

Views: 9826

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

Dates are not stored with any for at - at least if you are using a DATE or TIMESTAMP format, which you really should be. You can just use the raw column in the order by, something like:

select to_char(date_field, 'DD.MM.YYYY')
from my_table
order by date_field;

If you have it stored as a VARCHAR, which I can't stress enough would be a bad thing, you'd have to convert to a date for the order by and to a DATE and back to a VARCHAR, something like:

select to_char(to_date(varchar_field, 'DD-MM-RR HH24:MI:SS'), 'DD.MM.YYYY')
from my_table
order by to_date(varchar_field, 'DD-MM-RR HH24:MI:SS')

Upvotes: 5

Related Questions