[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle select query -> month based

Posted on 2007-07-28
8
Medium Priority
?
6,355 Views
Last Modified: 2013-12-19
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!
0
Comment
Question by:CSecurity
  • 3
  • 3
  • 2
8 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 1000 total points
ID: 19585233
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 19585234
where yourfield >= to_date('2007/07/01', 'YYYY/MM/DD')
and yourfield < to_date('2007/08/01', 'YYYY/MM/DD')
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 19585235
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 17

Author Comment

by:CSecurity
ID: 19585509
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
 
LVL 17

Author Comment

by:CSecurity
ID: 19585546
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19585615
>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
 
LVL 17

Author Comment

by:CSecurity
ID: 19585987
how can I define to return jul of for example 2007?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19586087
if your format is really like that:
where yourfield >= '2007/07/01'
and yourfield < '2007/08/01'
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month19 days, 13 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question