Oracle Deriving MD5 incorrectly when DateTime Concatenated with Text

user_1966702 0
Viewed 103

Please consider this SQL on Oracle 12c

select to_date('01-02-2020','MM-DD-YYYY'),
standard_hash (to_date('01-02-2020','MM-DD-YYYY'), 'MD5') Only_Date_MD5,
to_date('01-02-2020 12:34:56','MM-DD-YYYY HH:MI:SS'),
standard_hash (to_date('01-02-2020 12:34:56','MM-DD-YYYY HH:MI:SS'), 'MD5') DateTime_MD5,
standard_hash (to_date('01-02-2020','MM-DD-YYYY') || 'SomeText', 'MD5') Date_Concat_Text_MD5,
standard_hash (to_date('01-02-2020 12:34:56','MM-DD-YYYY HH:MI:SS') || 'SomeText', 'MD5') DateTime_Concat_Text_MD5
from dual;

Output

SOME_DATE                   01/02/2020
ONLY_SOME_DATE_MD5          6D44D021F4D2CACA3DBEC6E88AEEB7AD
SOME_DATETIME               01/02/2020 12:34:56
SOME_DATETIME_MD5           F8FDBBC5181E79B99A1EE13CB71A1D46
DATE_CONCAT_TEXT_MD5        **FE7DA8E96A7233A33F03CC592A929011**
DATETIME_CONCAT_TEXT_MD5    **FE7DA8E96A7233A33F03CC592A929011**

Why is Oracle MD5 returning same value for a Date concatenated with text and DateTime(with same Date) concatenated with same text. It is discarding the time portion of DateTime while deriving the MD5.

md5 oracle sql
2 Answers

This has nothing to do with standard_hash(). The issue is the implicit conversion of a date to a string.

When you convert a date implicitly to a string (or using to_char() with no format), then the result is only the date portion. So this:

select to_date('01-02-2020 12:34:56', 'MM-DD-YYYY HH:MI:SS') || 'abc'
from dual

returns:

02-JAN-20abc

For your purposes, I would strongly recommend using to_char() to convert back to a more detailed representation. You could also use to_timestamp() instead -- the default representation would include the time.

So:

select to_timestamp('01-02-2020 12:34:56', 'MM-DD-YYYY HH:MI:SS') || 'abc'
from dual

returns:

02-JAN-20 12.34.56.000000000abc
user_11440350

A good practice (or may be a workaround) to calculate a hash code of concatenated columns with a different data type (which leads inevitable to a data type conversion) is to set explicitly before this step the NLS settings to the most covering options.

Example 

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD.MM.YYYY HH24:MI:SSXFF';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'DD.MM.YYYY HH24:MI:SSXFF TZR';

This will 1) not eat up some parts of the data in the conversion and 2) will produce a deterministic result independent of the session setting.

You may need to store the previous values are recover them after the concatenation.

This step will ensure an expected result of your sample data.

The other thing you shoud consider is to use a special concatenation delimiter (a string that doesn't appear in the data)

Example

 col1||chr(10)||col1

This will ensure that the two rows with col1 NULL, col2 'A' and col1 'A', col2 NULL will produce a different hash code after the concatenation.

user_48081220
Related