Skip to Main Content
  • Questions
  • want to get month value from date value column

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: August 03, 2008 - 11:31 pm UTC

Last updated: October 10, 2013 - 5:56 pm UTC

Version: 8.1.7

Viewed 100K+ times! This question is

You Asked

hi all,
i had a column with date datatype.
in my column the dates are in 4/1/2007 format.
now i want month value from that column and date value from that column.
month and year are runtime parameters
plz help me.

thanks in advance,
radha

and Tom said...

no, in your column you have a 7 byte piece of information that includes

the century
the year
the month
the day
the hour
the minute
the second

4/1/2007 is a character string, you would use the format 'fmmm/dd/yyyy' to retrieve a date in that format, in that string - but the data isn't stored like that at all, it is binary and converted into a string using some format.


I don't know what you mean by "month and year are runtime parameters", you just described that you "want month value from that column and date value from that column" (eg: you want OUTPUTS, you don't have INPUTS - also known as parameters)


I don't know what a "date value" means in this context. To get the "month" from a DATE datatype you simply:

to_char( dt_column, 'mm' ) -- returns 01..12
to_char( dt_column, 'MON' ) -- returns JAN, FEB, ... DEC in your language
to_char( dt_column, 'Month' ) -- returns January, ... December in your language

and so on - you use any of the date formats (documented in the sql reference guide) and "pretty print" the string.

Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 06, 2012 - 11:48 am UTC

Thank u

eddy, February 29, 2012 - 10:16 am UTC

Hi all, after reading this, if i want to check the month can i simply make this

to_char( dt_column, 'Month' ) = '||v_month||'

which v_month equals to 'January'?

I got no value after the query, which suppose to give me many rows....
Thanks all
Tom Kyte
February 29, 2012 - 10:42 am UTC

huh?

No clue what you mean here - there is no example, nothing.

Also, that better not be a sql snippet there - no bind variables???? You have to be kidding right?


If you want the rows for a given month, that would be:

declare
   l_month  number;
begin

l_month := 1;

for x in (select * from t where to_number(to_char(dt_column,'MM')) = l_month)
loop
 ....

eddy, February 29, 2012 - 11:00 am UTC

This is my sample query

create or replace
PROCEDURE "MyProc" ----------------
(
v_month in varchar2,
mySurveyCusrsor in out myPackage.myCursor
)
IS
w_survey varchar2(150);
strsql varchar2(10000);
BEGIN

if v_blnEdisi != '' or v_blnEdisi is not null then
w_blnEdisi := ' AND to_char(surveyDate, ''Month'' ) = '''||v_month||'''';
end if;

strsql := ' SELECT name, surveyDate, from myTab where 1 = 1'''||w_survey||'';

open mySurveyCusrsor for strsql;
END MyProc;

I got no result, which in myTab there are data with the surveyDate '2012-1-01', '2011-1-01', '2012-2-01', '2011-2-01', '2011-3-01', '2012-3-01'
And I want to get all data which has srveyDate month on 'March'

Thanks before

eddy, February 29, 2012 - 11:03 am UTC

Sorry all, I type the query wrong, i mean this one.

create or replace
PROCEDURE "MyProc" ----------------
(
v_month in varchar2,
mySurveyCusrsor in out myPackage.myCursor
)
IS
w_survey varchar2(150);
strsql varchar2(10000);
BEGIN

if v_month != '' or v_month is not null then
w_survey := ' AND to_char(surveyDate, ''Month'' ) = '''||v_month||'''';
end if;

strsql := ' SELECT name, surveyDate, from myTab where 1 = 1'''||w_survey||'';

open mySurveyCusrsor for strsql;
END MyProc;

Tom Kyte
February 29, 2012 - 11:47 am UTC

ok, you should *never* code what you've code - ever.

You are easily subject to sql injection
you are generating non-scalable code that runs slow and eats shared pool memory for lunch.

there is literally nothing good to say about this bit of code - it makes many of the biggest mistakes.

problems with it:

a) subject to sql injection
b) doesn't use binds
c) v_month != '' will *never* be true by the way, this is not sqlserver
d) 'Month' format would have blank padding, need to use a different format
e) your building of strsql doesn't work at all, you quoted w_survey in the building of it, making your where clause a string


please read this article to see how to do this properly and safely (no sql injection, no scalability issues, best plan possible, doesn't eat memory for lunch)
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html


here is how that procedure might be written:

ops$tkyte%ORA11GR2> create table myTab ( name varchar2(10), surveyDate date );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into myTab
  2  select 'name ' || level, add_months( trunc(sysdate,'y'), level-1 )
  3    from dual
  4  connect by level <= 11;

11 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace PROCEDURE "MyProc"
  2  ( v_month in varchar2,
  3    mySurveyCursor in out sys_refcursor
  4  )
  5  IS
  6      l_sql long := 'select name, surveydate from myTab ';
  7  BEGIN
  8  
  9      if ( v_month is not null )
 10      then
 11          l_sql := l_sql || q'| where to_char(surveyDate, 'fmMonth' ) = :x |';
 12      else
 13          l_sql := l_sql || ' where (1=1 or :x is null)';
 14      end if;
 15  
 16      open mySurveyCursor for l_sql using v_month;
 17  end;
 18  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2> exec "MyProc"( 'January', :x )

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print x

NAME                           SURVEYDAT
------------------------------ ---------
name 1                         01-JAN-12

ops$tkyte%ORA11GR2> exec "MyProc"( 'December', :x )

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print x

no rows selected

ops$tkyte%ORA11GR2> 



On lines 9-14 - we use the technique from the above reference article to build a query using BIND VARIABLES. This is not subject to sql injection, this is scalable and shared pool friendly.

On line 11 we use a format modifier (fm) to get the trailing blanks to not be part of our to_char (you should use an ordinal number - it would be more "global" - January isn't spelled the same everywhere!

Shimmy, February 29, 2012 - 12:53 pm UTC

In the above code you have "q'| where to_char(sysdate, 'fmMonth' ) = :x |'", What's the purpose of "q"?
if ( v_month is not null )then
   l_sql := l_sql || <b>q</b>'| where to_char(surveyDate, 'fmMonth' ) = :x |';
else
   l_sql := l_sql || ' where (1=1 or :x is null)';
end if;

Tom Kyte
February 29, 2012 - 1:02 pm UTC

ops$tkyte%ORA11GR2> select q'*Now I can have 'single quotes' in my string*' from dual;

Q'*NOWICANHAVE'SINGLEQUOTES'INMYSTRING*'
-------------------------------------------
Now I can have 'single quotes' in my string




easier method for having a string with embedded single quotes in it.


a character string literal can either be

'hello there'

or

q'<any character>Hello There<that same character>'

like

q''how's this for easy''




http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#i42617

kgkk

hjgh, May 09, 2012 - 12:12 am UTC

jhgh

Date

Shaik, October 10, 2013 - 5:24 pm UTC

1)select to_char(hiredate,'Month') from emp where to_char(hiredate,'Month')='September';
The output is-------->
TO_CHAR(H
---------
September
September
-------------------------------------------
2)select to_char(hiredate,'Month') from emp where to_char(hiredate,'Month')='November'
The output is---------->
no rows selected
Anyone plz
Tom Kyte
October 10, 2013 - 5:56 pm UTC

September just happens to be the month with the longest name...

by default, all date formats return things fixed width (leading zeros on numbers, fixed width day/month fields, etc)

ops$tkyte%ORA12CR1> select '"' || to_char(hiredate,'Month') || '"',
  2         '"' || to_char(hiredate,'fmMonth') || '"',
  3         '"' || to_char(hiredate,'Day') || '"',
  4         '"' || to_char(hiredate,'fmDay') || '"',
  5         '"' || to_char(hiredate,'DD') || '"',
  6         '"' || to_char(hiredate,'fmDD') || '"'
  7    from emp
  8  /

'"'||TO_CHA '"'||TO_CHA '"'||TO_CHA '"'||TO_CHA '"'| '"'|
----------- ----------- ----------- ----------- ---- ----
"December " "December"  "Wednesday" "Wednesday" "17" "17"
"February " "February"  "Friday   " "Friday"    "20" "20"
"February " "February"  "Sunday   " "Sunday"    "22" "22"
"April    " "April"     "Thursday " "Thursday"  "02" "2"
"September" "September" "Monday   " "Monday"    "28" "28"
"May      " "May"       "Friday   " "Friday"    "01" "1"
"June     " "June"      "Tuesday  " "Tuesday"   "09" "9"
"December " "December"  "Thursday " "Thursday"  "09" "9"
"November " "November"  "Tuesday  " "Tuesday"   "17" "17"
"September" "September" "Tuesday  " "Tuesday"   "08" "8"
"January  " "January"   "Wednesday" "Wednesday" "12" "12"
"December " "December"  "Thursday " "Thursday"  "03" "3"
"December " "December"  "Thursday " "Thursday"  "03" "3"
"January  " "January"   "Saturday " "Saturday"  "23" "23"

14 rows selected.



you can use the fm (format modifier) in the date mask to change this default behavior.

Date

Shaik, October 11, 2013 - 5:00 am UTC

Thx a lot
I got it
Thx once again
Regards,

------------Shaik

Data Type

Shaik, October 13, 2013 - 6:31 pm UTC

oda = new OracleDataAdapter("myproc", ocon);
ds = new DataSet();
oda.SelectCommand.CommandType = CommandType.StoredProcedure;
oda.SelectCommand.Parameters.AddWithValue("p_uname", OracleType.VarChar).Value = TxtBox1.Text;
oda.SelectCommand.Parameters.AddWithValue("p_rques", OracleType.VarChar).Value = TxtBox2.Text;
oda.SelectCommand.Parameters.AddWithValue("p_rans", OracleType.VarChar).Value = TextBox3.Text;
oda.SelectCommand.Parameters.Add("p_pwd", OracleType.VarChar).Direction = ParameterDirection.Output;
oda.Fill(ds, "enquiry");
--------------------------------------------------------
Procedure
-----------
create or replace procedure myproc(p_uname varchar2,p_rques varchar2,p_rans varchar2,p_pwd out varchar2)
is
begin
select pwd into p_pwd from reg where uname=p_uname and rques=p_rques and rans=p_rans;
end;
/
------------>error----------------
Parameter 'p_pwd': No size set for variable length data type: String.

A reader, October 17, 2013 - 10:55 am UTC

Helpful

Compare months from dates

A reader, April 21, 2014 - 4:00 am UTC

Hi,
I was trying to compare months from two different dates.
I could do like to_char(date_column,'mm') = to_char(sysdate,'mm') or extract(month from date_columm) = extract(month from sysdate), but I lose my index on "date_column".
Can you share any logic to compare so that I can use my index.

Thanks in advance.

Russell, April 30, 2014 - 3:49 am UTC

If you want rows just from the current month, eg THIS April, then the following will do it:

select *
from tbl
where date_column >= trunc(sysdate,'MONTH')
and date_column < last_day(sysdate)+1;

This will do a range scan of an index on date_column.

If however you want rows from EVERY April, ie: Apr-2014, Apr-2013, Apr-2012 etc, then you will need a special index on this:

create index idx_mth on tbl(extract(month from date_column));

Then as long as you use exactly the same formula in your query, this index will be used. ie:

select *
from tbl
where extract(month from date_column) = 4;

A reader, March 02, 2018 - 10:49 am UTC

tnx