-1

I have this value:

Thu Jan 01 01:00:00 UZT 1970

I need this value from the above value:

Thu Jan 1970

Need Substring from this value.

hotfix
  • 3,376
  • 20
  • 36
Ramiz Tariq
  • 387
  • 10
  • 27
  • What type is this value stored as? – Mureinik Apr 04 '19 at 06:46
  • 1
    What have you tried so far – Jens Apr 04 '19 at 06:48
  • 1
    I hope you are not storing that value in a `varchar` column - that should be a `date` or `timsetamp`. And then reformatting the display gets as easy as applying `to_char()` on the column when selecting it –  Apr 04 '19 at 07:10

2 Answers2

1

Looks like a string. If that's so, here's one (actually, two) options:

SQL> with test (col) as
  2    (select 'Thu Jan 01 01:00:00 UZT 1970' from dual)
  3  select substr(col, 1, instr(col, ' ', 1, 2)) || substr(col, -4) result,
  4         regexp_substr(col, '(\w+ ){2}') || regexp_substr(col, '\d+$') result2
  5  from test;

RESULT       RESULT2
------------ ------------
Thu Jan 1970 Thu Jan 1970

SQL>

[EDIT]

How to convert it to a different format? By applying TO_DATE (to convert "Thu Jan 1970", which is a string, to a valid DATE format), and then TO_CHAR with appropriate format mask to get what you want.

SQL> with test (col) as
  2    (select 'Thu Jan 01 01:00:00 UZT 1970' from dual),
  3  inter as
  4    (select substr(col, 1, instr(col, ' ', 1, 2)) || substr(col, -4) result,
  5            regexp_substr(col, '(\w+ ){2}') || regexp_substr(col, '\d+$') result2
  6     from test
  7    )
  8  select to_char(to_date(result, 'dy mon yyyy', 'nls_date_language = english'), 'dd-mm-yyyy') new_result
  9  from inter;

NEW_RESULT
----------
01-01-1970

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • How to change into "DD-MM-YYYY" – Ramiz Tariq Apr 04 '19 at 07:01
  • You're welcome. I've modified my answer, have a look, please. – Littlefoot Apr 04 '19 at 07:08
  • I try in SQL Plus works fine. But when I try in Oracle Forms Text Field Item Date Column Data Type "CHAR" then getting error when-button-press trigger raised error. You can see code from this link https://stackoverflow.com/questions/55487932/frm-50026-date-must-be-entered-in-a-format-like-dd-mon-yyyy – Ramiz Tariq Apr 04 '19 at 09:00
1

Use TO_DATE then TO_CHAR:

SELECT TO_CHAR(
         TO_DATE(
           'Thu Jan 01 01:00:00 UZT 1970',
           'Dy Mon DD HH24:MI:SS "UZT" YYYY'
         ),
         'Dy Mon YYYY'
       ) AS substring
FROM   DUAL

| SUBSTRING    |
| :----------- |
| Thu Jan 1970 |

db<>fiddle here

If you want it as DD-MM-YYYY format then change the format model used in TO_CHAR to 'DD-MM-YYYY' but if you are going to be storing it in the database it would be better to just store it as a date value rather than as a formatted string.

I have some columns "UZT" and some "UZST" then getting error on "UZST"

If those are the only time zones and you are not worried if there is an hours difference between the two zones then you can use:

SELECT TO_CHAR(
         TO_DATE(
           REPLACE( your_column, 'UZST', 'UZT' ),
           'Dy Mon DD HH24:MI:SS "UZT" YYYY'
         ),
         'Dy Mon YYYY'
       ) AS substring
FROM   your_table

or if you need to account for the time zones (replace the time zone values as appropriate):

SELECT TO_CHAR(
         TO_TIMESTAMP_TZ(
           REPLACE(
             REPLACE(
               your_column,
               'UZST',
               '+05:00'
             ),
             'UZT',
             '+05:00'
           ),
           'Dy Mon DD HH24:MI:SS TZH:TZM YYYY'
         ) AT TIME ZONE '+05:00',
         'Dy Mon YYYY'
       ) AS substring
FROM   your_table
MT0
  • 143,790
  • 11
  • 59
  • 117