CODEWITHSUNDEEP
sqloracle-database
sm86
sm86

Reputation: 87

Time stamp date to mm/dd/yyyy in oracle sql

I have a field st_ts that shows as 07/09/2021 5:20:52 PM. How do I get it to show as just 07/09/2021? I've tried TO_DATE('ST_TS','MM/DD/YYYY') but it isn't working. In there where clause I have to do TRUNC(ST_TS) = '09-JUL-2021' to select the date. Thanks.

Upvotes: 0

Views: 1702

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

It depends on what that column's datatype is. I presume it is DATE. If that's so, have a look at the following examples:

SQL> create table test (st_Ts date);

Table created.

SQL> insert into test values (to_date('07/09/2021 05:20:52', 'mm/dd/yyyy hh24:mi:ss'));

1 row created.

Altering the session and setting date format mask:

SQL> alter session set nls_date_format = 'mm/dd/yyyy';

Session altered.

SQL> select * from test;

ST_TS
----------
07/09/2021

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

Session altered.

SQL> select * from test;

ST_TS
-------------------
09.07.2021 05:20:52

Applying TO_CHAR function with desired format mask:

SQL> select to_char(st_ts, 'mm/dd/yyyy') result from test;

RESULT
----------
07/09/2021

Truncating DATE value "resets" time portion to midnight:

SQL> select trunc(st_ts) result2 from test;

RESULT2
-------------------
09.07.2021 00:00:00

SQL>

Therefore, you have various options. It depends on what you're up to. If you're using some reporting tool, I'd suggest you to set field's format mask there. If you just want to display it differently, use TO_CHAR. For the whole session length, alter the session.

Upvotes: 3

Related Questions