Solved

Oracle Date Fuction

Posted on 2014-07-28
4
579 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 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
capture vmstat info and insert it into an oracle table 31 35
SQL Syntax 14 34
join actual table rows based on the column 25 21
pl/sql parameter is null sometimes 2 14
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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, 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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

856 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