Home » SQL & PL/SQL » SQL & PL/SQL » using to_date command to display specific month (sql*plus, winXP)
using to_date command to display specific month [message #348447] Tue, 16 September 2008 16:18 Go to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

hi guys,
i just want to display the dates of a specific month from the table using TO_DATE command but i don't the format to use.
could anyone tell me how to write the syntax for that.

But i can do for a day of a week using TO_DATE.

thanks

With Regards
ukd
Re: using to_date command to display specific month [message #348449 is a reply to message #348447] Tue, 16 September 2008 16:21 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions183.htm#sthref2315

You need to learn how to Read The Fine Manual to find answers to your questions.

[Updated on: Tue, 16 September 2008 16:22] by Moderator

Report message to a moderator

Re: using to_date command to display specific month [message #348452 is a reply to message #348447] Tue, 16 September 2008 16:28 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
The "to_date" function takes a string and translates it into an Oracle DATE type, so what you're asking doesn't make sense.

If you have a table with a DATE type column and you want to display that date in a specific FORMAT, you need to use the to_char function.

For example...


  select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') from dual;
  select to_char(sysdate,'DD-MON-RR') from dual;
  select to_char(sysdate,'fmDay, Month DD, RRRR') from dual;



Will all display the current date information in various formats. Replace "sysdate" with the information from your DATE type column in your table.

HTH,
Ron
Re: using to_date command to display specific month [message #348464 is a reply to message #348447] Tue, 16 September 2008 17:15 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

first of all, thanks very much.
actually it was my mistake. apologise for that.

but how to display the dates of specific month then? i tried the following:

to_char(column_name, 'dd-12-yy')

to display the month of december !

regards
ukd
Re: using to_date command to display specific month [message #348469 is a reply to message #348447] Tue, 16 September 2008 18:33 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
  1* select 'december' from dual
SQL> /

'DECEMBE
--------
december
Re: using to_date command to display specific month [message #348474 is a reply to message #348447] Tue, 16 September 2008 20:50 Go to previous messageGo to next message
satm2008
Messages: 10
Registered: September 2008
Location: Toronto, Canada
Junior Member
I guess you are asking to display the name of the month value of given date column, in full form, right?
Try this one.

SELECT TO_CHAR(date_column,'MONTH') FROM DUAL;

OR

SELECT TO_CHAR(SYSDATE,'MONTH') FROM DUAL; 


The second one prints the name of the current month, SEPTEMBER.

Good luck Smile
Re: using to_date command to display specific month [message #348479 is a reply to message #348447] Tue, 16 September 2008 21:43 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
I think..you are searching something like this,

select to_char(column_name, 'dd-mm-yy') from table_name where to_char(column_name, 'mm')='12 ';
Re: using to_date command to display specific month [message #348645 is a reply to message #348479] Wed, 17 September 2008 08:30 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
tanmoy1048 wrote on Tue, 16 September 2008 22:43
I think..you are searching something like this,

select to_char(column_name, 'dd-mm-yy') from table_name where to_char(column_name, 'mm')='12 ';



As the code is written, it will never return anything.
Re: using to_date command to display specific month [message #348714 is a reply to message #348447] Wed, 17 September 2008 15:10 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

it returns "no rows selected".
Re: using to_date command to display specific month [message #348729 is a reply to message #348714] Wed, 17 September 2008 16:04 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
True, but this was just a typo, right
'mm')='12 ';
         ^
         |
       here!
Re: using to_date command to display specific month [message #348735 is a reply to message #348447] Wed, 17 September 2008 16:25 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

i tried the following one which i'm not sure giving the right query or not...

select
to_date('01-01-2000', 'day-mm-yyyy')
from
dual;
Re: using to_date command to display specific month [message #348759 is a reply to message #348645] Wed, 17 September 2008 21:48 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
dear joy_division, I think, you should show up your experience while commenting on something.

But anyway....thanx.
Re: using to_date command to display specific month [message #348821 is a reply to message #348735] Thu, 18 September 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ukdas wrote on Wed, 17 September 2008 23:25
i tried the following one which i'm not sure giving the right query or not...

select
to_date('01-01-2000', 'day-mm-yyyy')
from
dual;

SQL> select
  2  to_date('01-01-2000', 'day-mm-yyyy')
  3  from
  4  dual; 
to_date('01-01-2000', 'day-mm-yyyy')
        *
ERROR at line 2:
ORA-01846: not a valid day of the week

I think it is not correct.

Regards
Michel

Re: using to_date command to display specific month [message #348978 is a reply to message #348735] Thu, 18 September 2008 08:08 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
This may help.
FOO SCOTT>select to_char(sysdate,'day') from dual;

TO_CHAR(S
---------
thursday

FOO SCOTT>select to_char(sysdate,'dd') from dual;

TO
--
18

FOO SCOTT>

Re: using to_date command to display specific month [message #348988 is a reply to message #348447] Thu, 18 September 2008 08:53 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

how to get the day of a week from any specific date of the year in the system such as the following date

12-03-2006 ?

regards
ukd
Re: using to_date command to display specific month [message #348990 is a reply to message #348447] Thu, 18 September 2008 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Please RTFM

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions183.htm#sthref2315
Re: using to_date command to display specific month [message #349039 is a reply to message #348447] Thu, 18 September 2008 12:10 Go to previous messageGo to next message
orasuman7
Messages: 4
Registered: July 2008
Location: BANGALORE
Junior Member
SQL> select to_char(sysdate,'year/mm/dd') from dual;

TO_CHAR(SYSDATE,'YEAR/MM/DD')
------------------------------------------------
two thousand eight/09/18


SQL> select to_char(sysdate,'year/month/dd') from dual;

TO_CHAR(SYSDATE,'YEAR/MONTH/DD')
-------------------------------------------------------
two thousand eight/september/18
Re: using to_date command to display specific month [message #349040 is a reply to message #349039] Thu, 18 September 2008 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again follow forum guide and format your post.

Regards
Michel
Re: using to_date command to display specific month [message #349042 is a reply to message #348988] Thu, 18 September 2008 12:30 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ukdas wrote on Thu, 18 September 2008 09:53
how to get the day of a week from any specific date of the year in the system such as the following date



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

It also depends on your NLS setting.
Re: using to_date command to display specific month [message #350086 is a reply to message #349042] Wed, 24 September 2008 01:30 Go to previous messageGo to next message
bechara.daccache
Messages: 14
Registered: September 2008
Location: Lebanon
Junior Member

how to get the day of a week from any specific date of the year in the system such as the following date

12-03-2006 ?

regards
ukd


try:

select to_char(to_date('12-03-2006', 'dd-mm-yyyy') ,'day') from dual;

regards,
bechara
Re: using to_date command to display specific month [message #350089 is a reply to message #350086] Wed, 24 September 2008 01:43 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide.

Regards
Michel
Previous Topic: Subselect union
Next Topic: sql help
Goto Forum:
  


Current Time: Mon Dec 05 20:58:56 CST 2016

Total time taken to generate the page: 0.09633 seconds