TO_DATE识别日期

问题描述

好的，我有这个日期字符串:

Ok, I have this date string:

2016-05-31T23:00:00.000Z

例如，我希望能够使用它来搜索Oracle 12c表中的日期

I want to be able to use it to search on a date in an Oracle 12c table, for example

SELECT stuff
FROM TABLE
WHERE date_column > TO_DATE('2016-05-31T23:00:00.000Z', 'what goes here?');

我不知道这个日期是什么格式，有人可以帮忙吗?这可能很简单，但我似乎找不到它...

I can't figure out what format this date is in, can anyone help? This is probably simple, but I can't seem to find it...

这不是C#

推荐答案

如果您需要将表示UTC的字符串转换为本地时区，则需要执行一些步骤.起点是将 to_timestamp()与T和Z的字符文字一起使用，Oracle无法识别这些字符文字:

If you need the string representing UTC converted to your local time zone then you need to do a few steps. The starting point is to use to_timestamp() with character literals for the T and Z, which Oracle doesn't recognise:

select to_timestamp('2016-05-31T23:00:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
from dual;

TO_TIMESTAMP('2016-05-31T23:00:00.000Z','YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
-------------------------------------------------------------------------
2016-05-31 23:00:00.000

然后您可以使用 from_tz()声明无时区的值实际上是UTC:

Then you can state that timezone-less value is actually UTC with from_tz():

select from_tz(
  to_timestamp('2016-05-31T23:00:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'),
  'UTC')
from dual;

FROM_TZ(TO_TIMESTAMP('2016-05-31T23:00:00.000Z','YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'
--------------------------------------------------------------------------------
2016-05-31 23:00:00.000 UTC

然后您可以将其转换为您自己的时区:

Then you can convert it to your own time zone:

select from_tz(
  to_timestamp('2016-05-31T23:00:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'),
  'UTC') at time zone 'Europe/London'
from dual;

FROM_TZ(TO_TIMESTAMP('2016-05-31T23:00:00.000Z','YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'
--------------------------------------------------------------------------------
2016-06-01 00:00:00.000 EUROPE/LONDON

如果您希望将其恢复为日期数据类型，可以对其进行转换:

If you want it back as a date datatype you can cast it:

select cast(from_tz(
  to_timestamp('2016-05-31T23:00:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'),
  'UTC') at time zone 'Europe/London' as date)
from dual;

CAST(FROM_TZ(TO_TIMESTAMP('2016-05-31T23:00:00.000Z','YYYY-MM-DD"T"HH24:MI:SS.FF
--------------------------------------------------------------------------------
2016-06-01 00:00:00

这篇关于TO_DATE识别日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！