Skip to Main Content
Forums

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to concatenate time to a date

user633278Jan 8 2010 — edited Jul 24 2013
Hi

I have a third party reporting, where I need to pass dates to my oracle database in order to execute a query. The query itself will be executed by the reporting tool. However, in the database the date has the time included in it, and what I would like to do is to add to the sql query a default time element when the date is passed. Therefore, when the date 06-Jan-2010 has been entered in by the user, then the time 23:59:00 is added to the query as well.

How do I concatenate the time element in a sql query when a date is being passed?

Thanks

Comments

Tubby 
select to_date ('06-Jan-2010' || ' 23:59:00', 'dd-mon-yyyy hh24:mi:ss') from dual
Is one way....

Just noticed that you're passing a DATE variable and a STRING ... so this covers that :)
select to_date ( to_char(DATE_VARIABLE, 'dd-mon-yyyy') || ' 23:59:00', 'dd-mon-yyyy hh24:mi:ss') 
from
(
  select to_date('06-Jan-2010', 'dd-mon-yyyy') as DATE_VARIABLE 
  from dual
)
Edited by: Tubby on Jan 8, 2010 3:32 PM
1 person found this helpful
fsitja
If you have received a date and want to concatenate with a varchar2 with the time: 
with t as (
select to_date('01-08-2009', 'MM-DD-YYYY') date1 from dual)
-- sample data
select to_date(to_char(date1, 'MM-DD-YYYY') || ' 23:59:00', 'MM-DD-YYYY HH24:MI:SS')
  from t
Hoek
How do I concatenate the time element in a sql query when a date is being passed?
You could also try another strategy if you have a date and you want to add/control the time component:
SQL> select sysdate "date_and_time"
  2  ,      trunc(sysdate) "date_only"
  3  ,      trunc(sysdate)+ (1-1/60/24) "date_and_added_time" --tomorrow minus a minute
  4  from dual
  5  /

date_and_time       date_only           date_and_added_time
------------------- ------------------- -------------------
09-01-2010 00:47:41 09-01-2010 00:00:00 09-01-2010 23:59:00

1 row selected.
Edited by: hoek on Jan 9, 2010 12:51 AM typo
Vara_Sagiraju

Thanks for the post Tubby..

Anirban Mondal

As you are trying to run a query with hard coded timestamp of 23:59:00 , I am assuming you
want to run comparison between dates. If that is the case, then we need to compare the dates
from the database to the date which will be provided from 3rd party reporting query.

Let,s say I have a table test with 2 columns data (varchar2(100)) and date_of_data_input (date).


SQL> desc test
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DATE_OF_INPUT                                      DATE
DATA                                               VARCHAR2(50)
I have some data in the table like:

DATE_OF_INPUT           DATA
------------------------             ------
23-07-2013 01:25:40       test2
22-07-2013 01:25:40       test3
21-07-2013 01:25:40       test4
20-07-2013 01:25:40       test5


If I need to query the table based on the date of date_of_data_input then I might query the database
like this:

select * from test
where date_of_input < to_date(
(select to_char(sysdate - 2,'DD-MM-YYYY') || ' 23:59:00' from dual),'DD-MM-YYYY HH24:Mi:SS');

Here in case of sysdate, I can use any string of 'DD_MM_YYYY. format which is valid. In your case,
this date binding will be done by the 3rd party tool I think.

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 21 2013
Added on Jan 8 2010
5 comments
81,248 views