We help IT Professionals succeed at work.
Private
Troubleshooting Question

How to get today's date in Oracle using TO_DATE and SYSDATE

100 Views
Last Modified: 2020-10-01
I want to update stop_time with today's date(sysdate) but still stamp it always with 11:00:00 PM. How can I accomplish this? is TO_CHAR a better way to go?

UPDATE WO_TASK_LABOR SET NOTES = 'TERMINATION',STOP_TIME = to_date('31-AUG-20 11:00:00','dd-Mon-yy hh24:mi:ss') WHERE start_time is not null and stop_time is null
Comment
Watch Question

awking00Information Technology Specialist
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Maybe I'm confused. If you always want 11:00 pm as the time just modify your update statement to
UPDATE WO_TASK_LABOR SET NOTES = 'TERMINATION',STOP_TIME = to_date('31-AUG-20 23:00:00','dd-Mon-yy hh24:mi:ss') WHERE start_time is not null and stop_time is null
or
UPDATE WO_TASK_LABOR SET NOTES = 'TERMINATION',STOP_TIME = to_date('31-AUG-20 11:00:00AM','dd-Mon-yy hh24:mi:ss') WHERE start_time is not null and stop_time is null
i.e. Make the string for the to_date function match the format mask, HH24 or AM/PM
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
just use 23 instead of 11, since you are using 24H in format

STOP_TIME = to_date('31-AUG-20 23:00:00','dd-Mon-yy hh24:mi:ss') 
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
OOPs, the second one was meant to be -
UPDATE WO_TASK_LABOR SET NOTES = 'TERMINATION',STOP_TIME = to_date('31-AUG-20 11:00:00PM','dd-Mon-yy hh24:mi:ss') WHERE start_time is not null and stop_time is null
and both need to use MON in the mask or Aug in the string.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Still got it wrong -
UPDATE WO_TASK_LABOR SET NOTES = 'TERMINATION',STOP_TIME = to_date('31-AUG-20 11:00:00PM','dd-MON-yy hh:mi:ssAM') WHERE start_time is not null and stop_time is null
HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
@HainKurt
What's the difference between what you posted and what I posted prior to that?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
They mentioned sysdate and today's date.  I believe the question is how to get it without hard coding a string so you can keep messing with formats if you want but I don't think it matters.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
@awking00

i did not see your post when I submitted, 3 min apart - reading/writing the post

+ there is difference, SPACE character before AM/PM sign :) 
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
No problem. The space doesn't matter as long as it's in both the format mask and the string or that it's not in both the format mask and the string. I think the min issue here is for troubleshooting to understand the need for the format mask to represent the string and the string to match the format string.
AUG in the string needs MON in the format mask
Aug in the string needs Mon in the format mask
11:00:00 PM needs hh:mi:ss AM in the format mask
23:00:00 needs hh24:mi:ss in the format mask

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.