CODEWITHSUNDEEP
oracle-databasedate-format
Faradox
Faradox

Reputation: 179

Oracle: group by month

I have such problem, I am working with Oracle database. in one of my tables there is a column in timestamp format like 06.01.14 08:54:35 I must select some data by grouping this column only by month. the result of query for the column if 06.01.14 then must be Jan.2014 in MM.YYYY format, I tried such query

SELECT to_char(ctime,'mm.yyyy') from table_name

but the result is not in date format, it is a string format. I could not do it with to_date function

Can you help me

Upvotes: 2

Views: 2021

Answers (2)

Michael Broughton
Michael Broughton

Reputation: 4055

Of course any variation on to_char will result in a string format. That's what it does. You are, I think, confusing how a value is displayed and the data type behind it.

Now if you have a DATE field then you can use

TRUNC(cTime,'MON')

which will truncate to the first of the month and leave it in date format. How you display it is then up to you. But if you are expecting a DATE value to not have a day/hour/minute/second component - then I'm afraid that you don't understand the data type.

Upvotes: 1

C8H10N4O2
C8H10N4O2

Reputation: 19005

If ctime is of type TIMESTAMP:

SELECT EXTRACT(month from ctime) "month" from table_name

edit: if you want month and year like "Jan - 2014", I think all you need is:

SELECT TO_CHAR(ctime, 'FMMon - YYYY') from table_name

(see http://www.techonthenet.com/oracle/functions/to_char.php)

If ctime is a string (e.g. type VARCHAR) you'll need to convert to timestamp first (so above, instead of ctime, replace with TO_TIMESTAMP(ctime, "MM.DD.YY HH:MM:SS")

HTH

Upvotes: 2

Related Questions