CODEWITHSUNDEEP
sqloracle-databasedate
Imran Hemani
Imran Hemani

Reputation: 629

Oracle Timestamp - date format not recognized

I have a column in my table which is of type Timestamp. while converting this field to the format: 2003-08-09T05:48:37+05:30, I am using the following query:

select 
TO_CHAR(CONSUMER_DLY_TIME, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')
from oms_cust_ord_head;

it gives me the error: "date format not recognized"

How to resolve this ?

Upvotes: 1

Views: 2932

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

A TIMESTAMP value does not contain any time zone information, thus you cannot display it.

Which time zone do you want do be shown?

For time zone of database operating system you can use:

SELECT 
   TO_CHAR(CONSUMER_DLY_TIME, 'YYYY-MM-DD"T"HH24:MI:SS')||TO_CHAR(SYSTIMESTAMP, 'TZH:TZM')

or for you current session time zone:

SELECT 
   TO_CHAR(CONSUMER_DLY_TIME, 'YYYY-MM-DD"T"HH24:MI:SS')||TO_CHAR(CURRENT_TIMESTAMP, 'TZH:TZM')

SELECT 
   TO_CHAR(CAST(CONSUMER_DLY_TIME AS TIMESTAMP WITH TIME ZONE), 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')

SELECT 
   TO_CHAR(CONSUMER_DLY_TIME, 'YYYY-MM-DD"T"HH24:MI:SS')||TZ_OFFSET(SESSIONTIMEZONE)

Upvotes: 1

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

You should use

select trunc(<timestamp_column>) from your_table

Read Format Models in detail.

Note: Format in which the date will be displayed depends on your session parameter nls_date_format.

Upvotes: 0

Related Questions