Doing an update on a date in oracle

Posted on 2007-10-15
Last Modified: 2012-06-27
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.
Question by:BernyB
    LVL 18

    Accepted Solution

    update ...  set col1 = to_date('11/08/2007 15:22:10','dd/mm/yyyy hh24:mi:ss')  
    where ...
    LVL 73

    Expert Comment

    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.
    LVL 34

    Expert Comment

    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
    LVL 47

    Assisted Solution

    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.
    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now