Oracle Date Datatype, Transformed to 'Yyyy-Mm-Dd Hh24:Mi:Ss Tmz' Through SQL

Oracle Date datatype, transformed to 'YYYY-MM-DD HH24:MI:SS TMZ' through SQL

There's a bit of confusion in your question:

• a Date datatype doesn't save the time zone component. This piece of information is truncated and lost forever when you insert a TIMESTAMP WITH TIME ZONE into a Date.
• When you want to display a date, either on screen or to send it to another system via a character API (XML, file...), you use the TO_CHAR function. In Oracle, a Date has no format: it is a point in time.
• Reciprocally, you would use TO_TIMESTAMP_TZ to convert a VARCHAR2 to a TIMESTAMP, but this won't convert a Date to a TIMESTAMP.
• You use FROM_TZ to add the time zone information to a TIMESTAMP (or a Date).
• In Oracle, CST is a time zone but CDT is not. CDT is a daylight saving information.
• To complicate things further, CST/CDT (-05:00) and CST/CST (-06:00) will have different values obviously, but the time zone CST will inherit the daylight saving information depending upon the date by default.

So your conversion may not be as simple as it looks.

Assuming that you want to convert a Date d that you know is valid at time zone CST/CST to the equivalent at time zone CST/CDT, you would use:

SQL> SELECT from_tz(d, '-06:00') initial_ts,
  2         from_tz(d, '-06:00') at time zone ('-05:00') converted_ts
  3    FROM (SELECT cast(to_date('2012-10-09 01:10:21',
  4                              'yyyy-mm-dd hh24:mi:ss') as timestamp) d
  5            FROM dual);

INITIAL_TS                      CONVERTED_TS
------------------------------- -------------------------------
09/10/12 01:10:21,000000 -06:00 09/10/12 02:10:21,000000 -05:00

My default timestamp format has been used here. I can specify a format explicitely:

SQL> SELECT to_char(from_tz(d, '-06:00'),'yyyy-mm-dd hh24:mi:ss TZR') initial_ts,
  2         to_char(from_tz(d, '-06:00') at time zone ('-05:00'),
  3                 'yyyy-mm-dd hh24:mi:ss TZR') converted_ts
  4    FROM (SELECT cast(to_date('2012-10-09 01:10:21',
  5                              'yyyy-mm-dd hh24:mi:ss') as timestamp) d
  6            FROM dual);

INITIAL_TS                      CONVERTED_TS
------------------------------- -------------------------------
2012-10-09 01:10:21 -06:00      2012-10-09 02:10:21 -05:00

How do I insert date in YYYY-MM -DD HH24:MI:SS in Oracle

INSERT INTO TABLE (COL1, ...) VALUES (
    to_date('2001-12-30 17:27:59', 'YYYY-MM-DD HH24:MI:SS')
); 

You submit a string and the date format to TO_DATE, and then use that in your VALUES clause of a TABLE INSERT command.

Oracle stores it as a DATE, there is no stored format of the date. When you query it out, you have the ability to get it back in the format of your choice, or leave it to the database or session parameters to be your default date format.

How to convert date stored as VARCHAR2 to 'MM/DD/YYYY HH24:MI:SS'?

You should convert in date first and then in char

 SELECT TO_CHAR(TO_DATE(your_var_char_date,'MM/DD/YYYY  HH24:MI:SS'), 'MM/DD/YYYY  HH24:MI:SS') 
 FROM  your_table;

obviuosly if is necessary only the conversion to date should be used

SELECT TO_DATE(your_var_char_date,'MM/DD/YYYY  HH24:MI:SS')
FROM  your_table;

Convert Date into simple DD-MM-YYYY HH24:MI:SS format in SQL

You Can use the below Query to get ur required output

SELECT TO_CHAR(TO_TIMESTAMP_TZ('Fri Dec 04 01:00:00 CST 2015', 
                               'DY Mon DD HH24:MI:SS TZR YYYY'), 'DD-MM-YYYY HH24:MI:SS') 
       FROM DUAL;

OUTPUT

04-12-2015 01:00:00

Get local variable to show date + days with YYYY-MM-DD hh24:mi:ss

Your v_datum is of date type, and p_dagar is a number. You can add a number to a date and the result will be a date.
So, your function body will be simply,

v_återdatum := v_datum + p_dagar;

This returned date will contain the time part, but may not be displayed based on the NLS_DATE_FORMAT parameter, which you can check using this query.

select *
  from v$nls_parameters
 where parameter = 'NLS_DATE_FORMAT';

It is always better to explicitly specify the display format using TO_CHAR function.

select to_char(get_återdatum(7), 'yyyy-mm-dd hh24:mi:ss') from dual;

I would like to keep my table's data type as date but I want my table to show date and time format in the same column

You don't have to do anything; that column already contains both date and time. It is the front-end that is supposed to display it as you want. Here are some examples:

SQL> create table test (datum date);

Table created.

SQL> insert into test (datum) values (to_date('2018-02-02 21:05:18', 'YYYY-MM-DD HH24:MI:SS'));

1 row created.

SQL> select * from test;

DATUM
--------
02.02.18

SQL> select to_char(datum, 'hh24:mi:ss') only_time,
  2    to_char(datum, 'dd-mon-yy') date_format_1,
  3    to_char(datum, 'yyyy-mm-dd hh24:mi:ss') date_and_time
  4  from test;

ONLY_TIM DATE_FORMAT_1      DATE_AND_TIME
-------- ------------------ -------------------
21:05:18 02-vel-18          2018-02-02 21:05:18

SQL>

Which means: use TO_CHAR with appropriate format mask, if you use SELECT statement. If it is about e.g. Oracle Apex, Forms or Reports, modify item's format mask.

Alternatively, you may talk to DBA to change NLS settings for the whole database, or you can do it for your session:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select datum from test;

DATUM
-------------------
02.02.2018 21:05:18

PL/SQL Oracle v9 - How to change Timestamp into Date mm/dd/YYYY

Dates (and timestamps) do not have a format - they are represented internally by 7 or 8 bytes for a date or 20 bytes for a timestamp.

If you want to format a date or a timestamp then you will have to convert it to a string either explicitly with TO_CHAR():

SELECT TO_CHAR( date_column, 'MM/DD/YYYY' )
FROM   your_table;

Or implicitly within whatever client program (i.e. you've tagged plsqldeveloper) you are using - in which case check the preferences within your IDE.

---

Related Topics