CODEWITHSUNDEEP
sqloracle-database
jscriptor
jscriptor

Reputation: 845

ORACLE date format with +00:00 part

I am trying to execute this oracle delete statement 

DELETE FROM mySchema.mytable
WHERE myCol= 'abc'
  and DATE_TIME_UTC >= to_date('2011-07-30 00:00:00+00:00','yyyy-mm-dd HH24:MI:SS+TZH:TZM')
  and DATE_TIME_UTC <= to_date('2011-07-31 23:55:00+00:00','yyyy-mm-dd HH24:MI:SS+TZH:TZM')

but I get

ORA-01821: date format not recognized.

Most likely because of the +TZH:TZM part, but I could find any thing on google to get the right format.

I would appreciate your help in setting the right format or pointer to where I can find the right format

Thank you

Upvotes: 0

Views: 448

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59513

DATE data type does not contain any time zone information. Use TIMESTAMP WITH TIME ZONE instead, i.e. to_timestamp_tz('2011-07-30 00:00:00+00:00','yyyy-mm-dd HH24:MI:SS+TZH:TZM')

DELETE FROM mySchema.mytable
WHERE myCol= 'abc'
  and DATE_TIME_UTC >= to_timestamp_tz('2011-07-30 00:00:00+00:00','yyyy-mm-dd HH24:MI:SS+TZH:TZM')
  and DATE_TIME_UTC <= to_timestamp_tz('2011-07-31 23:55:00+00:00','yyyy-mm-dd HH24:MI:SS+TZH:TZM')

However, I assume DATE_TIME_UTC is a DATE value rather than TIMESTAMP WITH TIME ZONE. In this case you must convert your timestamp value into UTC like this:

DELETE FROM mySchema.mytable
WHERE myCol= 'abc'
  and DATE_TIME_UTC >= SYS_EXTRACT_UTC(to_timestamp_tz('2011-07-30 00:00:00+00:00','yyyy-mm-dd HH24:MI:SS+TZH:TZM'))
  and DATE_TIME_UTC <= SYS_EXTRACT_UTC(to_timestamp_tz('2011-07-31 23:55:00+00:00','yyyy-mm-dd HH24:MI:SS+TZH:TZM'))

or you can do it the other way around (most likely with less performance):

DELETE FROM mySchema.mytable
WHERE myCol= 'abc'
  and FROM_TZ(DATE_TIME_UTC, 'UTC') >= to_timestamp_tz('2011-07-30 00:00:00+00:00','yyyy-mm-dd HH24:MI:SS+TZH:TZM')
  and FROM_TZ(DATE_TIME_UTC, 'UTC') <= to_timestamp_tz('2011-07-31 23:55:00+00:00','yyyy-mm-dd HH24:MI:SS+TZH:TZM')

Of course, since all your times are given in UTC you can simplify it and you don't need to consider time zone information at all:

DELETE FROM mySchema.mytable
WHERE myCol= 'abc'
  and DATE_TIME_UTC >= to_date('2011-07-30 00:00:00','yyyy-mm-dd HH24:MI:SS')
  and DATE_TIME_UTC <= to_date('2011-07-31 23:55:00','yyyy-mm-dd HH24:MI:SS')

But the solutions above are more generic.

Upvotes: 3

Related Questions