Home » SQL & PL/SQL » SQL & PL/SQL » Taking time from current day/month/year 06:00:00 to sysdate(Current Time) (Oracle 10 g)
Taking time from current day/month/year 06:00:00 to sysdate(Current Time) [message #662847] Sat, 13 May 2017 02:42 Go to next message
baranwalb
Messages: 2
Registered: May 2017
Junior Member
Sir,
I have a table 'disturbance' in which mill_start_time and mill_stop_time has values
I want to know the delay from start of morning shift. Here morning shift starts from 06:00 hours. So I write a query as given below

select D.DISTBEG Mill_Stop,D.DISTEND Mill_Start ,(D.DISTEND-D.DISTBEG)*1440 down_Minute from disturbance d
where D.DISTBEG between to_date('13-05-2017 06:00:00','dd-mm-yyyy HH24:MI:SS') and sysdate


In the above query I have to feed current date as 13-05-2017. I want to replace it with sysdate's day-mm-yyyy. How can I modify my query?
Thanks in advance.
Re: Taking time from current day/month/year 06:00:00 to sysdate(Current Time) [message #662850 is a reply to message #662847] Sat, 13 May 2017 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

If I understand what you want is the current date at 00:00:00, then:
TRUNC(SYSDATE)

If you want the current date at 06:00:00, then just add 6/24 to the previous expression.

Re: Taking time from current day/month/year 06:00:00 to sysdate(Current Time) [message #662851 is a reply to message #662850] Sat, 13 May 2017 05:46 Go to previous messageGo to next message
baranwalb
Messages: 2
Registered: May 2017
Junior Member
thank you sir for your reply.
I have a table DISTRUBANCE in which fields are DISTBEG & DISTEND . These two fields are of type DATE and stores start of delay and end of delay. Our production shift time starts from 6 am in morning. I want to run a sql to calculate delay time in minutes through a batch file in daily basis. I'll run that batch file at a specified time. So I want to take start of delay from current date with time as 6 am.
So what changes I have to make in my sql? my code is given below.
select D.DISTBEG Mill_Stop,D.DISTEND Mill_Start ,(D.DISTEND-D.DISTBEG)*1440 down_Minute from disturbance d
where D.DISTBEG between to_date('13-05-2017 06:00:00','dd-mm-yyyy HH24:MI:SS') and sysdate
I don't want to give 13-05-2017, it should take sysdate's dd-mm-yyyy
Thanks in advance.
Re: Taking time from current day/month/year 06:00:00 to sysdate(Current Time) [message #662852 is a reply to message #662851] Sat, 13 May 2017 06:38 Go to previous message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I did answer to this and I remark that you didn't take time to read the links I pointed you to and follow our guidelines.

Previous Topic: Multiple values in one-many relationship
Next Topic: filesystem_like_logging is not in ddl of table
Goto Forum:
  


Current Time: Sat Oct 20 23:58:02 CDT 2018