Oracle TO_DATE not keeping format

Question

Since this seems more of a syntax question, my search didn't produce any useful results.

I'm attempting to make a query that gets European data from an American database using GMT time. I need to get the European time, but can't seem to get it working correctly.

Lets say "myDateField" = '01-01-2010'

SELECT TO_CHAR(myDateField + (60 / 1440), 'Mon" "DD", "YYYY')

Produces exactly what I want, but I need it to stay as a date.
Produces: 'Jan 01, 2010'

SELECT TO_DATE(TO_CHAR(myDateField + (60 / 1440)), 'Mon" "DD", "YYYY')

Merits a "Not a valid Month" error.
Produces: Error

SELECT TO_DATE(TO_CHAR(myDateField + (60 / 1440), 'Mon" "DD", "YYYY'), 'Mon" "DD", "YYYY')

Works, but the formatting is lost.
Produces: '01-01-2010'

How can I format this so that I can get the result the first statement gives, but also keep it as a date? Or is there a better method altogether?

EDIT:
Just as an example of this particular query working under a different circumstance... This is the same query, but instead of converting to European time, it converts to another timezone in North America.

SELECT TO_DATE(TO_CHAR(NEW_TIME(myDateField, 'GMT', 'MDT')), 'Mon" "DD", "YYYY')

Produces exactly what I want, while keeping the data as a date.
Produces: 'Jan 01, 2010'

In summary:

Is there any way to take "myDateField + (60 / 1440)", or use the "FROM_TZ" Keyword, and make the result use the 'Mon" "DD", "YYYY' format, while retaining it's Date type?

Answer 1

I think it is important to realize that the formatting you desire takes effect as part of converting from DATE to CHAR types. So your requirement, "setting the format but keeping it as date" does not make sense - not in Oracle it don't.

If you just want to add some time interval to a date, and keep it a date, this is all that is needed:

myDateCol + (60 / 1440) -- add one hour to the date

and if you want to format that in european style, it becomes

TO_CHAR(myDateCol + (60 / 1440), 'MON DD, YYYY')

but of course, it will then be string, a human readable representation of the actual date value. And because of the format, this actually lacks the time informatioin.

You can of course convert that back toa date:

TO_DATE(TO_CHAR(myDateCol + (60 / 1440), 'MON DD, YYYY'), 'MON DD, YYYY')

but seriously - there is no point - because the time part is not present in the string, this date will actually have 00:00:00 as time. So you just lost information.

This becomes clear when you do things like this:

TO_CHAR(myDateCol + (60 / 1440), 'MON DD, YYYY HH24:MI:SS')

compare that too:

TO_CHAR(
  TO_DATE(
    TO_CHAR(myDateCol + (60 / 1440), 'MON DD, YYYY HH24:MI:SS')
  , 'MON DD, YYYY'
  )
, 'MON DD, YYYY HH24:MI:SS'
)

(the last exprression demonstrates how you truncated the time fiels)

Answer 2

A date is just a date - it doesn't have an intrinsic string format. What it does have is a default format, that is, when you query a date into some program or library that extracts it as a string, there is a default setting as to how it will be formatted.

One approach is to simply query the date and have your client program format it. For example, in Java you can use the DateFormat class.

But you can also try ALTER SESSION SET NLS_DATE_FORMAT = 'Mon dd, yyyy'

and that might work.

Edit: Your second query returned a not a valid month error for me. Did you run that after following my ALTER SESSION suggestion?

Answer 3

New answer, since the first one adressed the confusion over date vs char, not timezones.

You asked if there is a better way. I think there is: use oracle time zone support.

see: http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006333

Example query to convert a datetime to another timezone:

SELECT FROM_TZ(
         CAST(TO_DATE('1999-12-01 11:00:00','YYYY-MM-DD HH:MI:SS') AS TIMESTAMP)
       , 'America/New_York'
       )
       AT TIME ZONE 'America/Los_Angeles' "West Coast Time"
FROM DUAL;

Using these functions ensure that the math is correct. After that you can of course format it the way you want to, for example:

SELECT TO_CHAR(
         FROM_TZ(
           CAST(TO_DATE('1999-12-01 11:00:00','YYYY-MM-DD HH:MI:SS') AS TIMESTAMP)
         , 'America/New_York'
         )
         AT TIME ZONE 'America/Los_Angeles' 
       , 'MON DD, YYYY') "West Coast Time"
FROM DUAL;

...but like I pointed out in the first answer, the result here will be a CHAR, not a date.