CODEWITHSUNDEEP
sqloracle-databasedate-format
user12094588
user12094588

Reputation: 321

How to get the time format I need?

At the moment I get a date in this format:

2019-10-07

That is, it looks like this

TO_CHAR("DATE_BEGIN", 'YYYY-MM-DD')

How to do so in order to receive a date in this format:

Wed Oct 07 2019 00:00:00 GMT 0500

I tried to do so:

 TO_CHAR("DATE_BEGIN", 'DY MON DD YYYY HH24:MI:SS') like '%Wed Aug 28 2019 00:00:00 GMT 0500%'

But something is missing, because it does not work.

Upvotes: 2

Views: 66

Answers (2)

ravioli
ravioli

Reputation: 3823

I'm not sure if you can return both GMT and the 0500. This should get you close: 

SELECT TO_CHAR(CURRENT_TIMESTAMP AT TIME ZONE 'GMT', 'Dy Mon DD YYYY HH:MI:SS TZR') 
FROM dual

Result:

Mon Oct 07 2019 08:24:44 +00:00

SQL Fiddle

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

I think this is the format you'd like :

select to_char( systimestamp at time zone 'GMT','Dy Mon dd yyyy hh24:mi:ss TZR') 
  from dual;

or directly use :

select to_char( systimestamp,'Dy Mon dd yyyy hh24:mi:ss TZR') 
  from dual;

or for your current substitution date use a casting :

select to_char( cast( date'2019-10-07' as timestamp) at time zone 'GMT',
                      'Dy Mon dd yyyy hh24:mi:ss TZR') 
  from dual;

Demo

Upvotes: 2

Related Questions