• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 16041
  • Last Modified:

Doing an update on a date in oracle

I want to update a date that has the time appended to the end like this -
10/08/2007 17:23:16
What is the syntax if I wanted to update it to say
11/08/2007 15:22:10
Something like
update ...  set col1 = to_date('11/08/2007','dd/mm/yyyy')  ????
where ...
what do I need in the question marks to update the time. Thanks for your help.
2 Solutions
Jinesh KamdarCommented:
update ...  set col1 = to_date('11/08/2007 15:22:10','dd/mm/yyyy hh24:mi:ss')  
where ...
remember, dates don't have time "appended to them",  a date is always a year-month-day-hour-minute-second  combination.

even if you TRUNC a date, it still has a time  of 00:00:00  (midnight)

so you simply include the time formatting with the year/month/day formatting to construct your entire date.
Mark GeerlingsDatabase AdministratorCommented:
If your NLS_date_format setting is: "MM/DD/YYYY HH24:MI:SS" (this setting would give you a date displayed exactly as you entered it in your question) then you can simply use this syntax:
update [table_name]
set [date_column+name] = '11/08/2007 15:22:10'

Oracle will then do the implicit data conversion for you to convert the character string '11/08/2007 15:22:10' to a "date" value that includes the time.

Be aware that Oracle date columns (and variables) *always* include the time component as stsuber indicated.  Oracle allows a great deal of flexibilty though in how the date and/or time values get displayed.  Your programs can always use an explicit "to_char" or "to_date" with a particular date format mask if you wish.  If you don't use an explicit "to_char" or "to_date", then Oracle will use the value you have for: NLS_DATE_FORMAT, but this can be set in lots of different places, and a more-specific place always over-rides a more general place.  Here is a list of most of the places where this value can be set from the more-specific to the more general:
1. in application logic in your program
2. in a startup routine in your application
3. in your client's (or application server's) environment variables (UNIX or Linux) or registry (Windows)
4. in an after-logon trigger in the database
5. in your database server's environment variables (UNIX or Linux) or registry (Windows)
6. in your database instance parameter file
Use jinesh_kamdar solution.

The other solution
set [date_column+name] = '11/08/2007 15:22:10'
 is risky and possibly will work only
on computers in USA that uses this NLS structure for
dates as default. If you are outside USA (try to find job in USA :)) )
the NLS setting can fail the statement and even enter wrong dates before signalling
for bad input dates.
Forced accept.

EE Admin

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now