Link to home
Start Free TrialLog in
Avatar of jvera524

asked on

Sorting Dates in String Format


I have a query which exports data from Oracle to Excel.  One of the columns is a datetime column.  MM/DD/YYYY HH:MM:SS.  I only need the date.  In the SQL I changed it read
to_char(FieldName,'MM/DD/YYYY').  This works fine but it converts it to a string, which is creating problems when sorting.  

Any thoughts...thanks as always for the help.

Avatar of Sean Stuber
Sean Stuber

Excel can sort your data for you on import and can read mm/dd/yyyy strings as a date

or, sort your data by fieldname (in native date format) as your export it.
In your order by clause on your query, don't order by the converted column, order by the source:

ORDER BY FieldName


Avatar of jvera524


There are multiple date fields.  Someone looking at the file may want to sort by one of the date columns.  Also the wierdest thing( at least for me), when I sort the the field it seems to work fine the way I currently have it, but when the person who needs this report sorts the exact file on her computer it does not sort correctly.  For example, it have several 2011 dates then a 2010 date and then 2011 again.  Please let me know if any more information is needed.
in the sql add

order by DateTimeColumn
Sounds like Excel is not recognizing the column as a date.  You probably need to change the column format in Excel so that it will sort properly.  Or, as suggested here, use the query to sort the data, so the sort in Excel is not required.
Sorting in the query would not matter, because there are other date fields and depending on who is looking at the file would determine which date field they would want to sort by.  Despite the fact the are converted to string I formatted the column as date. This did not seem to do anything.
than get the data as


Your problem is not with Oracle, it is with Excel.

Not sure how you can force a data type in Excel.

How are you getting the data to Excel?
The data is coming in from Oracle to Excel using a SQL query (Microsoft Query).  The date must be formatted in this format to_char(FieldName,'MM/DD/YYYY').

Why would it work fine on my conputer but on the two other computers I tried the sorting does not work? This is what is confusing me most.

post a sample excel file with data inside...
I agree with HainKurt's suggestion: extract the "date" value as: to_char(FieldName,'YYYY/MM/DD').  That way, even a character sort in a text editor or word processor (or Excel) will keep the "date" values in the correct order.
Avatar of johnsone
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In oracle it is a datetime field but in the query I am converting to a string because I only want the date portion. So by the time it gets to Excel it is a string.  Any idea where I look at the settings? I went to Excel Options but did not notice anything.

Thanks johnsone for the article I will take a look.
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In excel, convert the text string to a date value using the VALUE function.


= 40590

which is the 'datevalue' for 16 Feb 2011.

If the database is spitting out the dates in an unusual order (you mention Month / Day / Year, putting the least significant digit in the middle?) then you might get a #VALUE error from the VALUE function.

If so, then split the date out first, then convert.  If the date is in A1 as a string "02/16/2011" then this will return the datevalue for 16 Feb 2011:


See also attached for the same formula split out into parts to make it easier to understand.



Select the date range > Data > Text to Columns > Next > Next > Check on Date and select MDY > Finish.