Solved

Oracle Date Fuction

Posted on 2014-07-28
4
568 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
4 Comments
 
LVL 76

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 73

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 34

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 34

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now