Oracle select query -> month based

Hello!

I have a column which have time and date in it. assume now() function in VB.

It is like this:
2007/07/28 06:54:41

It have about 10000 records with a column called last which have date and time in format I wrote above.

Now I want to select data from oracle which is in a range.

In real words for example I want to select data which is for October or January or May... I want to select from Oracle month based. How it is possible with date and time I have in my table?

I need exact query.

Thanks from now!
LVL 17
CSecurityAsked:
Who is Participating?
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
if that field in oracle table is of date datatype, then
you can use the below :

select col1, col2
from table1
where to_char(mydate_col,'mon') = 'jan';
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
where yourfield >= to_date('2007/07/01', 'YYYY/MM/DD')
and yourfield < to_date('2007/08/01', 'YYYY/MM/DD')
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
select col1, col2
from table1
where to_char(mydate_col,'mm') between '01' and '04';

this is to get data for months jan,feb,mar, apr
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
CSecurityAuthor Commented:
it says mydate_col is wrong.. It says invalid number...

The date is stored in nvarchar and in my table mydate_col is last...

I wrote your statement and I replaced mydate_col to last but it says invalid number...

It should be date column? But it is nvarchar column... How this is possible now?
0
 
CSecurityAuthor Commented:
Wooww!
Thanks to all! I solved it:

select nid from tblNews where to_char((to_date(xdate, 'YYYY/MM/DD hh24:mi:ss')), 'mon') = 'jul';
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Thanks to all! I solved it
>select nid from tblNews where to_char((to_date(xdate, 'YYYY/MM/DD hh24:mi:ss')), 'mon') = 'jul';

just to note tat that will return the data of the month july of ANY year!
why did you use nvarchar for a date value? you reall should make it a datetime data type instead!
0
 
CSecurityAuthor Commented:
how can I define to return jul of for example 2007?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if your format is really like that:
where yourfield >= '2007/07/01'
and yourfield < '2007/08/01'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.