[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Sorting Dates in String Format

Hello,

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.

John
0
jvera524
Asked:
jvera524
  • 4
  • 4
  • 4
  • +4
2 Solutions
 
sdstuberCommented:
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.
0
 
johnsoneSenior Oracle DBACommented:
In your order by clause on your query, don't order by the converted column, order by the source:

ORDER BY FieldName

or

ORDER BY TRUNC(FieldName)
0
 
jvera524Author Commented:
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
HainKurtSr. System AnalystCommented:
in the sql add

order by DateTimeColumn
0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
jvera524Author Commented:
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.
0
 
HainKurtSr. System AnalystCommented:
than get the data as

to_char(FieldName,'YYYY/MM/DD')

:)
0
 
johnsoneSenior Oracle DBACommented:
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?
0
 
jvera524Author Commented:
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.

0
 
HainKurtSr. System AnalystCommented:
post a sample excel file with data inside...
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
johnsoneSenior Oracle DBACommented:
If it sorts correctly in one place and not another, then I would suspect some sort of setting in Excel.

Not sure if it will help, but I found this link on sorting in Excel.

http://office.microsoft.com/en-us/excel-help/troubleshoot-sorting-HP005204040.aspx

I don't believe pulling the data in a different format will help.  They "need" to see the data in MM/DD/YYYY format.

My suspicion is that Excel is not recognizing what you are importing as a date and that is preventing it from sorting properly.
0
 
jvera524Author Commented:
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.
0
 
HainKurtSr. System AnalystCommented:
when getting from oracle use

to_char(FieldName,'MM/DD/YYYY')  FieldName
-->
TRUNC(FieldName) FieldName

instead of converting to char. Then excel will (should) know it is date and sort should work fine...
0
 
AlanConsultantCommented:
In excel, convert the text string to a date value using the VALUE function.

Example:

=VALUE("16/02/2011")
= 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:

=DATE(MID(A1,FIND("/",A1,1)+FIND("/",MID(A1,FIND("/",A1,1)+1,999))+1,999),LEFT(A1,FIND("/",A1,1)-1),MID(A1,FIND("/",A1,1)+1,FIND("/",MID(A1,FIND("/",A1,1)+1,999))-1))

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

HTH,

Alan.
DB-Dates---Version-1.xls
0
 
krishnakrkcCommented:
Hi,

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

Kris
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 4
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now