Oracle 11g timestamp format mask

Posted on 2012-08-22
Medium Priority
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 hh:mi.ss') but I'm getting format errors, like ora-018... invalid month, etc.
Question by:Mike Johnson
  • 2
LVL 25

Expert Comment

ID: 38323729
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

ID: 38323742
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 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 38324613
>>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

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
The viewer will learn how to implement Singleton Design Pattern in Java.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

809 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