Home » SQL & PL/SQL » SQL & PL/SQL » day, week, month and year grouping
day, week, month and year grouping [message #261731] Thu, 23 August 2007 08:14 Go to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Hi

Oracle9i Release2

I have a table say Tab_A with date column say Col_Date

I want to group my query on the basis day, week, month and year on the basis of Col_Date.
Please advise how?

Wishes
J a w a d

Re: day, week, month and year grouping [message #261732 is a reply to message #261731] Thu, 23 August 2007 08:16 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Use the TO_CHAR function e.g. to_char(date, 'YYYY').
Re: day, week, month and year grouping [message #261741 is a reply to message #261732] Thu, 23 August 2007 08:30 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Rather than using to_char, you may find that EXTRACT is a good deal faster.

EXTRACT (YEAR FROM SYSDATE)
EXTRACT (MONTH FROM SYSDATE)
EXTRACT (DAY FROM SYSDATE)

Re: day, week, month and year grouping [message #262199 is a reply to message #261741] Sat, 25 August 2007 01:10 Go to previous messageGo to next message
weekend79
Messages: 188
Registered: April 2005
Location: Islamabad
Senior Member

Dear Cthulhu & pablolee

Thanks you very much.
Pablolee your solution work best for me + please also advise how can I extract week?

Thanks
J a w a d

Re: day, week, month and year grouping [message #262206 is a reply to message #262199] Sat, 25 August 2007 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't with extract function.
You have to use to_char with WW or IW format model.

Regards
Michel
Re: day, week, month and year grouping [message #262246 is a reply to message #262206] Sat, 25 August 2007 11:24 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Just for fun, there is also TRUNC.

SQL> ho cal 8 2007
    August 2007
 S  M Tu  W Th  F  S
          1  2  3  4
 5  6  7  8  9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31

SQL> SELECT SYSDATE, TRUNC(SYSDATE,'IW'), TO_CHAR(SYSDATE,'IW') FROM dual;

SYSDATE     TRUNC(SYSDA TO
----------- ----------- --
25-AUG-2007 20-AUG-2007 34

1 row selected.
icon1.gif  Re: day, week, month and year grouping [message #262305 is a reply to message #262246] Sun, 26 August 2007 01:52 Go to previous messageGo to next message
sqlc
Messages: 6
Registered: August 2007
Junior Member
Hi

could i get more insight on the extract function.


thanks in advance


sqlc
Re: day, week, month and year grouping [message #262308 is a reply to message #262305] Sun, 26 August 2007 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is missing in the link I posted?

Regards
Michel
Re: day, week, month and year grouping [message #262336 is a reply to message #261731] Sun, 26 August 2007 04:42 Go to previous messageGo to next message
sqlc
Messages: 6
Registered: August 2007
Junior Member
oh ya

it dosent seem to work
Re: day, week, month and year grouping [message #262338 is a reply to message #262336] Sun, 26 August 2007 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it dosent seem to work

What does this mean? It does or does not work?

Regards
Michel
Re: day, week, month and year grouping [message #262339 is a reply to message #261731] Sun, 26 August 2007 05:09 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
In Oracle/PLSQL, the extract function extracts a value from a date or interval value.

The syntax for the extract function is:

EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )

You can only extract YEAR, MONTH, and DAY from a DATE.

You can only extract TIMEZONE_HOUR and TIMEZONE_MINUTE from a timestamp with a time zone datatype.

For example:

extract(YEAR FROM DATE '2003-08-22') would return 2003
extract(MONTH FROM DATE '2003-08-22') would return 8
extract(DAY FROM DATE '2003-08-22') would return 22
Re: day, week, month and year grouping [message #262340 is a reply to message #262339] Sun, 26 August 2007 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wow! You are pretty good to copy the documentation.
It'd be better if you say where you got this from.

Regards
Michel
Re: day, week, month and year grouping [message #262342 is a reply to message #262340] Sun, 26 August 2007 05:22 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
You do not like to hit web site more, so i copy it. SO u need not hit that page.
Embarassed

If u want to see that form see bellow

http://www.techonthenet.com/oracle/functions/extract.php

[Updated on: Sun, 26 August 2007 05:23]

Report message to a moderator

Re: day, week, month and year grouping [message #262392 is a reply to message #262339] Sun, 26 August 2007 23:05 Go to previous messageGo to next message
sqlc
Messages: 6
Registered: August 2007
Junior Member
i know there's somethin wrong in the way i'm interpreting it but i cant seem to work it out

the error i get is

ORA-00923: FROM keyword not found where expected

where did i go wrong in the syntax
Re: day, week, month and year grouping [message #262398 is a reply to message #261731] Sun, 26 August 2007 23:38 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Strange. I see error message, however I cannot find a statement it caused.
I would also believe the Oracle compiler, that it contains syntax error.
But, without knowing the statement, it is quite impossible to say (without mind reading ability or pure guessing) where did you go wrong in the syntax.
Re: day, week, month and year grouping [message #262440 is a reply to message #262392] Mon, 27 August 2007 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Extract only exists in 9i and up.

Regards
Michel
Re: day, week, month and year grouping [message #262451 is a reply to message #262342] Mon, 27 August 2007 01:45 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
I'm not sure we should trust a site that refers to "Oracle PL/SQL" as "Oracle/PLSQL", doesn't mention the version and contains advertising popups.

As Michel said, you'll find it in the 9i SQL documentation but not in 8i.
Previous Topic: Help Needed
Next Topic: Seperating the single column values into two columns
Goto Forum:
  


Current Time: Sun Dec 11 08:29:05 CST 2016

Total time taken to generate the page: 0.08569 seconds