Return to Question

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database

SELECT DECODE (TO_CHAR (MIN (date), 'YYYYMMDD'), '', NULL, TO_DATE (TO_CHAR (MIN (date), 'YYYYMMDD') , 'YYYYMMDD')) FROM table WHERE id = 76423

which returns 02/03/29. The year should be 1929 and, in fact, that is what I get if I do

SELECT TO_DATE (TO_CHAR (MIN (date), 'YYYYMMDD') , 'YYYYMMDD') FROM table WHERE id = 76423

I don't understand why this should happen, as the date format is clearly specified. Can someone shed some light upon it? All in all this is as problem because the date returned by the DECODE query gets read as 02/03/2029 when it is used.

EDIT: The two conversions (from date to char and then again to date) here is due to the fact that the query as I put it here is really two different queries: the first one inserts a DATE in an auxiliar table's column which has a CHAR format; then another query takes this data to the final table, where it's stored as a DATE, and hence the second conversion. I simplified it here to make it more understandable. The DECODE is necessary as some of the rows don't have data on the date column.

Anyway, I think the best solution is to do ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD' as suggested by @Phil and @LeighRiffel, and then both queries (with and without DECODE) return the same date. Another possible workaround would be to leave the final data as CHAR instead of converting it to DATE, but this would have a large impact on the application I'm working on, so I'm sticking to the ALTER SESSION ... way.

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database

SELECT 
    DECODE(
        TO_CHAR(MIN(DATE), 'YYYYMMDD')
        , ''
        , NULL
        , TO_DATE(TO_CHAR(MIN(DATE), 'YYYYMMDD'), 'YYYYMMDD')
    )  
FROM TABLE  
WHERE id = 76423

which returns 02/03/29. The year should be 1929 and, in fact, that is what I get if I do

SELECT TO_DATE(TO_CHAR(MIN(DATE), 'YYYYMMDD'), 'YYYYMMDD')  
FROM TABLE  
WHERE id = 76423

I don't understand why this should happen, as the date format is clearly specified. Can someone shed some light on this? This is a problem since the date returned by the DECODE query gets read as 02/03/2029 when it is used.

EDIT: The two conversions (from date to char and then again to date) here is due to the fact that the query as I put it here is really two different queries: the first one inserts a DATE in an auxiliary table's column which has a CHAR format; then another query takes this data to the final table, where it's stored as a DATE, and hence the second conversion. I simplified it here to make it more understandable. The DECODE is necessary as some of the rows don't have data on the date column.

Anyway, I think the best solution is to do ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD' as suggested by @Phil and @LeighRiffel, and then both queries (with and without DECODE) return the same date. Another possible workaround would be to leave the final data as CHAR instead of converting it to DATE, but this would have a large impact on the application I'm working on, so I'm sticking to the ALTER SESSION ... way.

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database

SELECT DECODE (TO_CHAR (MIN (date), 'YYYYMMDD'), '', NULL, TO_DATE (TO_CHAR (MIN (date), 'YYYYMMDD') , 'YYYYMMDD')) FROM table WHERE id = 76423

which returns 02/03/29. The year should be 1929 and, in fact, that is what I get if I do

SELECT TO_DATE (TO_CHAR (MIN (date), 'YYYYMMDD') , 'YYYYMMDD') FROM table WHERE id = 76423

I don't understand why this should happen, as the date format is clearly specified. Can someone shed some light upon it? All in all this is as problem because the date returned by the DECODE query gets read as 02/03/2029 when it is used.

EDIT: As I see it now, it could seem strange this double conversion from date to char to date again. It's just a pair of queries done separately which I put together so as to identify the problem.

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database

SELECT DECODE (TO_CHAR (MIN (date), 'YYYYMMDD'), '', NULL, TO_DATE (TO_CHAR (MIN (date), 'YYYYMMDD') , 'YYYYMMDD')) FROM table WHERE id = 76423

which returns 02/03/29. The year should be 1929 and, in fact, that is what I get if I do

SELECT TO_DATE (TO_CHAR (MIN (date), 'YYYYMMDD') , 'YYYYMMDD') FROM table WHERE id = 76423

I don't understand why this should happen, as the date format is clearly specified. Can someone shed some light upon it? All in all this is as problem because the date returned by the DECODE query gets read as 02/03/2029 when it is used.

EDIT: The two conversions (from date to char and then again to date) here is due to the fact that the query as I put it here is really two different queries: the first one inserts a DATE in an auxiliar table's column which has a CHAR format; then another query takes this data to the final table, where it's stored as a DATE, and hence the second conversion. I simplified it here to make it more understandable. The DECODE is necessary as some of the rows don't have data on the date column.

Anyway, I think the best solution is to do ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD' as suggested by @Phil and @LeighRiffel, and then both queries (with and without DECODE) return the same date. Another possible workaround would be to leave the final data as CHAR instead of converting it to DATE, but this would have a large impact on the application I'm working on, so I'm sticking to the ALTER SESSION ... way.

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database

SELECT DECODE (TO_DATE (TO_CHAR (MIN (date), 'YYYYMMDD'), 'YYYYMMDD'), '', NULL, TO_DATE (TO_CHAR (MIN (date), 'YYYYMMDD') , 'YYYYMMDD')) FROM table WHERE id = 76423

which returns 02/03/29. The year should be 1929 and, in fact, that is what I get if I do

SELECT TO_DATE (TO_CHAR (MIN (date), 'YYYYMMDD') , 'YYYYMMDD') FROM table WHERE id = 76423

I don't understand why this should happen, as the date format is clearly specified. Can someone shed some light upon it? All in all this is as problem because the date returned by the DECODE query gets read as 02/03/2029 when it is used.

EDIT: As I see it now, it could seem strange this double conversion from date to char to date again. It's just a pair of queries done separately which I put together so as to identify the problem.

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database

SELECT DECODE (TO_CHAR (MIN (date), 'YYYYMMDD'), '', NULL, TO_DATE (TO_CHAR (MIN (date), 'YYYYMMDD') , 'YYYYMMDD')) FROM table WHERE id = 76423

which returns 02/03/29. The year should be 1929 and, in fact, that is what I get if I do

SELECT TO_DATE (TO_CHAR (MIN (date), 'YYYYMMDD') , 'YYYYMMDD') FROM table WHERE id = 76423

I don't understand why this should happen, as the date format is clearly specified. Can someone shed some light upon it? All in all this is as problem because the date returned by the DECODE query gets read as 02/03/2029 when it is used.

EDIT: As I see it now, it could seem strange this double conversion from date to char to date again. It's just a pair of queries done separately which I put together so as to identify the problem.