Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Specifying minute as optional in to_date

user13178950Jun 25 2013 — edited Apr 21 2017

Hi,

I have a DB column which stores the start time for one of my functionalities.The starttime can be stored as either 7AM or 7:00AM or it can be 7AM or 7:30AM and so on. That is the column can be of the format either HH:MIAM or HHAM. Now the problem is - the format i have given in the query is as below HHAM and this is throwing an error which i assume is due to the difference in format

select to_DATE(start_time,'HH:MIAM') works for 7:00AM

select to_DATE(start_time,'HHAM') works for 7AM

Is there anyway i can specify the 'minute' as optional in format so that a single query returns both 7AM and 7:30AM.Or how can i make this work in Oracle.I would require my query to return both results.Is there any way oracle will append : 00 if it finds that the time doesnt have minute specified.

Thanks in advance for the help,

This post has been answered by Frank Kulash on Jun 27 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 25 2013
Added on Jun 25 2013
7 comments
1,034 views