CODEWITHSUNDEEP
sqloracle-database
Caenerys
Caenerys

Reputation: 57

Sql Change Time in Query

I currently have the following Problem, working on an Oracle Database: I have 2 columns of an appointment I want to read: date_from and date_to. They have both DateTime as the datatype.
I need to adjust the time of the value though (and only the time, the date should stay the same).

date_from for example contains 10.10.2017 14:21:00 as a value,
but should be changed to the "start of the day" --> 10.10.2017 00:00:00

date_to for example contains 11.10.2017 11:47:00 as a value,
but should be changed to the "end of the day" --> 10.10.2017 23:59:59

Is this somehow possible to manipulate it this way? I can not do an Update or permanent change to the data. This Format is only needed for a Gantt Diagramm, I dont have an other way to change it.

Thank you in Advance!

Upvotes: 2

Views: 840

Answers (4)

Diego Souza
Diego Souza

Reputation: 548

If you need to show the result, try this:

select to_char(date_from, 'DD.MM.YYYY') ||' 00:00:00' as date_from,
       to_char(date_to,   'DD.MM.YYYY') ||' 23:59:59' as date_to
  from table_name

But you may need to compare intervals. In this case, you could discarts the time using trunc function:

select *
  from table_dates t,
       other_table o
 where trunc(o.some_date) between trunc(t.date_from) and trunc(t.date_to)

UPD: First, I did an implicit conversion of dates to string using TRUNC. But it can lead to inexpected result. Instead, explicitly use TO_CHAR with the format model you are expecting for your output and you do not need to use TRUNC. (Thank you @MT0 )

Upvotes: 1

nicolasl
nicolasl

Reputation: 441

Yes, you can achieve that with Oracle's TO_DATE and TO_CHAR functions. It will only depend if you need the result as a DATE or as a VARCHAR. The syntax would look like this for VARCHAR output:

TO_CHAR(date_from, 'DD.MM.YY') || ' 00:00:00'
TO_CHAR(date_to, 'DD.MM.YY') || ' 23:59:59'

If you need the DATE value from this, just add the TO_DATE funtion around it:

TO_DATE((TO_CHAR(date_from, 'DD.MM.YY') || ' 00:00:00'), 'DD.MM.YY HH24:MI:SS')
TO_DATE((TO_CHAR(date_to, 'DD.MM.YY') || ' 23:59:59'),  'DD.MM.YY HH24:MI:SS')

I did not test it, but it's pretty much it.

Does this helps?

Cheers

Nikao

Upvotes: 3

Nikhil
Nikhil

Reputation: 3950

this might be some help to you:

    SELECT to_char(current_timestamp,'yyyy-mm-dd hh:mm:ss') FROM dual;

    SELECt trunc(current_timestamp)||'  23:59:59' FROM dual;

Upvotes: -1

JohnHC
JohnHC

Reputation: 11195

So you want to return the start and end of the day?

select trunc(start_date) as day_start, -- Strips off the time part of the date
       trunc(end_date) + 1 - (1/86400) as day_end -- As above, but we add 1 day and minus 1 second
from My_Table

Also, Oracle has date formats of Date and Timestamp, no datetime

Upvotes: 0

Related Questions