Oracle 11g timestamp format mask

Posted on 2012-08-22
Last Modified: 2013-01-25
I have a report parameter that has to be of datetime format.  I'm I have a situation where customers imput the date as date format (reports are launched through a LMS).. I want to truncate the time portion essentially going from datetime to date  to filter the report dataset.

How does Oracle 11g handle default format if you input date into a dateime(timestamp) variable?   ex. 6/19/2012 ---->  6/19/2012 12:00:00 ?

I'm trying to format the timestamp as trunc(''6/19/2012 12:00:00', 'mm/dd/yyyy') but I'm getting format errors, like ora-018... invalid month, etc.
Question by:Mike Johnson
    LVL 25

    Expert Comment

    You can use TRUNC for most ... except for seconds.  For a DATE value it has a precision to seconds anyway - so there is nothing to do.  For a timestamp the sub-seconds can be removed by casting to DATE.
    SELECT sysdate,
           trunc(sysdate)       AS date_day,
           trunc(sysdate, 'hh') AS date_hour, 
           trunc(sysdate, 'mi') AS date_minute,
           trunc(current_timestamp)        AS ts_day,
           trunc(current_timestamp, 'hh')  AS ts_hour,
           trunc(current_timestamp, 'mi')  AS ts_minute,
           cast(current_timestamp AS date) AS ts_seconds
    FROM dual

    Open in new window

    LVL 25

    Expert Comment

    To format a value ... use TO_CHAR
    SELECT sysdate,
           to_char(sysdate, 'mm/dd/yyyy')            AS date_day,
           to_char(sysdate, 'mm/dd/yyyy hh24')       AS date_hour, 
           to_char(sysdate, 'mm/dd/yyyy hh24:mi')    AS date_minute,
           to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') AS date_full,
           to_char(current_timestamp, 'mm/dd/yyyy')               AS ts_day,
           to_char(current_timestamp, 'mm/dd/yyyy hh24')          AS ts_hour,
           to_char(current_timestamp, 'mm/dd/yyyy hh24:mi')       AS ts_minute,
           to_char(current_timestamp, 'mm/dd/yyyy hh24:mi:ss')    AS ts_seconds,
           to_char(current_timestamp, 'mm/dd/yyyy hh24:mi:ss.ff') AS ts_full
    FROM dual

    Open in new window

    LVL 76

    Accepted Solution

    >>How does Oracle 11g handle default format

    The 'default' format is specified by the parameter NLS_TIMESTAMP_FORMAT for timestamps and NLS_DATE_FORMAT for dates.

    Whatever these are set to is the format the string needs to be for implicit data conversions.

    Using the information above, get into the habit of doing explicit data type conversions:  TO_DATE and TO_TIMESTAMP to go from strings to date/timestamp and TO_CHAR to go to strings.
    LVL 53

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    If you haven’t already, I encourage you to read the first article ( in my series to gain a basic foundation of R and R Studio.  You will also find the …
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
    This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

    728 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

    20 Experts available now in Live!

    Get 1:1 Help Now