Oracle Date Fuction

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.
mrongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
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
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
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.