I have a date column in this format "03-FEB-16" in a database table. I want to query the database to get UTC format equivalent of that date value:
This is the UTC format I am looking for "2015-12-17T14:30:33Z"
6
-
What is the datatype of the column?Wernfried Domscheit– Wernfried Domscheit2016-02-03 20:22:18 +00:00Commented Feb 3, 2016 at 20:22
-
I used "SELECT cast(sentdate as timestamp) at time zone 'UTC' from table_name where id=22110626738" and got this output "03-FEB-16 04.24.31.000000000 PM UTC", however this is not what I want. I wanted "2015-12-17T14:30:33Z"sophist_pt– sophist_pt2016-02-03 21:47:01 +00:00Commented Feb 3, 2016 at 21:47
-
That was not my question. I asked "What is the datatype of the column? "Wernfried Domscheit– Wernfried Domscheit2016-02-04 09:03:12 +00:00Commented Feb 4, 2016 at 9:03
-
My apologies, I put the comment in the wrong place. The datatype is DATE.sophist_pt– sophist_pt2016-02-04 18:56:21 +00:00Commented Feb 4, 2016 at 18:56
-
OK, and what is the time zone of these date values?Wernfried Domscheit– Wernfried Domscheit2016-02-04 19:30:27 +00:00Commented Feb 4, 2016 at 19:30
|
Show 1 more comment
2 Answers
Try this:
SELECT TO_CHAR(cast(SYSDATE as timestamp) at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') FROM DUAL
Change SYSDATE to say SYSDATE-150 to test different times of the year
Comments
I think I found my answer but Please feel free to post alternative ways to do it.
The following query did the trick:
select to_char(sysdate,'YYYY-MM-DD')||'T'||to_char(sysdate,'HH24:MI:SS')||'Z' from dual
2 Comments
Wernfried Domscheit
You can write
select to_char(sysdate,'YYYY-MM-DD"T"HH24:MI:SS"Z"') from dual. However, this is only correct if your database server OS runs on UTC time zone.Wernfried Domscheit
Based on the information in your comments, your solution is correct.