Solved

Oracle Date Fuction

Posted on 2014-07-28
4
580 Views
Last Modified: 2014-07-28
Greeting,

I have a date field named tranx_date in Oracle and in the format of '14-JUN-25'. I need a query which will search table  TBL1 return all the records having tranx_date between ('mm/dd/yyyy') and ('mm/dd/yyyy+ 1 day). Please suggest.
Thanks.
0
Comment
Question by:mrong
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40224447
Dates don't have a 'format' until selected.

You should explicitly set the data types (and formats).

select ...
where tranx_date >= to_date('01/01/2014','mm/dd/yyyy') and tranx_date < to_date('01/01/2014','mm/dd/yyyy')+1
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40224493
assuming tranx_date is a string (since, as noted above, dates don't have formats, only string representations of them do)  try this...

select * from your_table
where to_date(tranx_date,'dd-MON-rr')  >=  to_date(some_datestring_variable,'mm/dd/yyyy')
and to_date(tranx_date,'dd-MON-rr') <= to_date(some_datestring_variable,'mm/dd/yyyy') + 1


Note the >= and <= might not be what you really want .  Compare to slightwv's above  >=  <

I used them on the assumption your data is a string (given your description of it having a format) and you wanted an end point-inclusive range.

His difference is on the assumption you data is actually a date but you are misinterpreting a default string representation as an actual internal format.  Also he is assuming your data is not truncated to midnight and hence you want all values for a day but not midnight of the following day.

Both interpretations can't be right, but either could be correct based on your description.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40224756
I'm guessing that you "have a date field named tranx_date in Oracle" and you see it "in the format of '14-JUN-25'".  As slightwv said, Oracle date columns do not include a format.  Oracle systems and programs can include a default date format, so you typically see dates displayed in a particular date format.  But, if this really is a "date" column, the date format is not saved in the database.

What format does this query return:
select sysdate from dual;

The format of that will show you your default date format.  But, just because your default date format may be dd-mon-yy, that doesn't mean you have to use that format in your queries.  As long as you provide a valid, explicit format mask in your query, you can use any date format that Oracle supports.

For example, these are all valid and will return the same results (if your column really is a "date" column, and if you provide values for "some_day" that match format mask in that query):

1. select * from [your_table] where tranx_date between to_date('&some_day','MM/DD/YYYY') and to_date('&some_day','MM/DD/YYYY') +1

2. select * from [your_table] where tranx_date between to_date('&some_day','DD/MM/YYYY') and to_date('&some_day','DD/MM/YYYY') +1

3. select * from [your_table] where tranx_date between to_date('&some_day','DD/MONYYYY') and to_date('&some_day','DD/MON/YYYY') +1
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40224886
An additional point is that a field in Oracle that is of type DATE also has a time component.  If the process that is inserting the data into the database is using SYSDATE to put the data in the field, then there is a time component stored as well and the queries already given may not get you what you want.  You may be missing the second day.

If not specified, the time defaults to 00:00:00.  The queries already given would need to be tweaked slightly to give you the second day if your processes are inserting a time component.  If they are not, and all dates are stored as 00:00:00 for time, then that shouldn't be an issue.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

733 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