Oracle Date Fuction


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.
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
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.
Mark GeerlingsDatabase AdministratorCommented:
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
johnsoneSenior Oracle DBACommented:
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.
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.