Adding a day in Oracle but losing hour and minute, and format is also changing

Question

I have a table tab1 in which a column col1 has data type VARCHAR2(50 BYTE) and this column has values like '9/27/21 18:05'

I want to add 1 day to this and I am expecting a result like '9/28/21 18:05'

If I do

TO_TIMESTAMP(col1,'MM/DD/YYYY HH24:MI') + INTERVAL '1' DAY

then I get '28-SEP-21 06.24.00.000000000 PM', and if I do

TO_DATE(col1,'MM/DD/YYYY HH24:MI') + INTERVAL '1' DAY'

then I get '28-SEP-21'.

Please note in both the above cases format is changing.

How can I get the result I want?

Answer 1

DATE and TIMESTAMP values are both binary data types that do NOT have a given format; therefore, when you convert a string to a DATE or a TIMESTAMP then the format you use is NOT stored.

If you want to convert it to a DATE or TIMESTAMP and then back to a string in the same format then, after adding the interval, you want to use TO_CHAR to convert back to a string with the required format.

For example:

SELECT TO_CHAR(
         TO_DATE(col1, 'MM/DD/RR HH24:MI') + INTERVAL '1' DAY,
         'MM/DD/RR HH24:MI'
       )
FROM   tab1

Note: If you use YYYY in the format model then 21 will be parsed as 21 AD and not as 2021 AD. Instead, you need to use YY or RR (depending on how you want values from the end of the last century to be handled).

Please note in both the above cases format is changing.

The format of your column is not changing, you have converted the strings to DATE and TIMESTAMP which are binary data type and do not have any format.

The user interface you are using (i.e. SQL/Plus or SQL Developer) tries to be helpful and rather than presenting you, the user, with binary data will use its internal rules to format the binary data as something you can read. SQL/Plus and SQL Developer will use the NLS_DATE_FORMAT session parameter for DATE values and the NLS_TIMESTAMP_FORMAT session parameter for TIMESTAMP values. These parameters can be set to different values for each user in each of their sessions so you should not rely on them to be consistent.

If you want a consistent format then wrap the date/timestamp in TO_CHAR to apply that consistent format.

Answer 2

You are converting your string into a date or timestamp, and adjusting it by a day. Your client then decides how to format that for display, usually using you session setting like NLS_DATE_FORMAT.

If you want to display (or store*) the value in a particular format then you should specify that, with to_char(), e.g.:

TO_CHAR(TO_DATE(col1,'MM/DD/YYYY HH24:MI') + INTERVAL '1' DAY,'MM/DD/YYYY HH24:MI')

09/28/0021 18:05

or if you want to suppress some leading zeros to match your original string you can toggle those with the FM modifier:

TO_CHAR(TO_DATE(col1,'MM/DD/YYYY HH24:MI') + INTERVAL '1' DAY,'FMMM/DD/YYYY HH24:FMMI')

9/28/21 18:05

As you can see in the output of first of those, and as @Aitor mentioned, the year comes out as 0021 rather than 21. That's because you used a four-digit YYYY mask for a 2-digit year value. In the second one the FM suppresses that, so it's less obvious. As you don't seem to care about the century it usually doesn't matter whether you use YY or RR - the exception maybe being if you happen to hit a leap year/day; but it's still better to have the mask match the string, so with RR:

TO_CHAR(TO_DATE(col1,'MM/DD/RR HH24:MI') + INTERVAL '1' DAY,'FMMM/DD/RR HH24:FMMI')

9/28/21 18:05

db<>fiddle

* But you should not be storing dates as strings. They should be stored as dates, and formatted as strings for display only. You shouldn't really be using 2-digit years any more either.

Answer 3

You said your column has this: 9/27/21, but you put a mask like YYYY. Be careful with that, because with YYYY, the year will be 21 BC...

Maybe you want RRRR in your date mask. RRRR means 2-digit years in the range 00 to 49 are assumed to be in the current century:

select to_char(to_date('9/27/2021 18:05','MM/DD/RRRR HH24:MI')+ INTERVAL '1' DAY,'MM/DD/YYYY HH24:MI') result 
from dual;

Result: 09/28/2021 18:05

I don't know what is your output format, but anyway, if you want your date formatted like VARCHAR, try this. With your column, is something like that

select to_char(to_date(col1,'MM/DD/RRRR HH24:MI') + 1,'MM/DD/YYYY HH24:MI') result 
from your_table;

Also you can use, instead of INTERVATL '1' DAY, a simple +1