Oracle: How to get a formatted string from a date?

simple

September 2, 2011

Development

No Comment

If you work with SQL on Oracle you sometimes want to extract additional informations from a date field and also limit this result to a distinct set.
So for example to get all distinct years of a date field from a data set, you can use the following statement, which uses a subquery:

We can further optimize the query, removing the subquery:

If you now only want to order the result set, but also group it, remember that the alias year is not known when “GROUP BY” is invoked. To work around this simply use the same “TO_CHAR” function, instead of the alias:

If you want to know the year, but want to preserve the data type date, you can do this by using the function”TRUNC” instead of “TO_CHAR” function:

Any comments or additions are welcome!

Related Posts

Eclipse: convert upper case text to lower case and viceversa with a simple shortcut

simple

November 7, 2012

Development, Java

No Comment

This is just a quick post to share these 2 little shortcuts to convert text from lower case to uppercase and vice-versa in Eclipse. Lower case: CTRL+SHIFT+Y (CMD+SHIFT+Y on Mac OS X) Upper case: CTRL+SHIFT+X (CMD+SHIFT+X on Mac OS X) Camel case: Anyone knows a shortcut? You can select one or more characters to convert.

Read More

OpenERP: Connect Kettle to OpenERP

[1] http://kettle.pentaho.com [2] http://ci.pentaho.com/view/Data%20Integration/job/Kettle/ [3] http://apps.openerp.com/addon/1833?filter=%7B%22order_by%22%3A+%22click_counter+desc%22%2C+%22author%22%3A+78%7D&page=0 [4] http://erpmoldeo.wordpress.com/2011/02/10/openerp-kettle-and-terminatooor/ [5] http://www.akretion.com/en/products-and-services/openerp-kettle-bi-connector-terminatooor [6] http://forums.pentaho.com/showthread.php?98831-Asking-OpenERP-module-for-pentaho-data-integration-(Kettle)

Read More

Leave a Reply