Wrong choice of function as it assumes there are only 365 days
in a year. This is documented in SQL reference.
Between the two timestamps there are 42704 days and 22 hours.
Convert that to microseconds and its hex value is what you want. To
get the STCK itself you multiply mic seconds by 4096.
If you are doing these types of date time arithmetic to be used
in recovery etc, you should also account for local time offsets.
Some sites may be using leap seconds to steer the clock away. That
may need to be accounted if precise estimate is required. If in zOS
use CONVTOD service macro to do the conversion. If in distributed
side use epoch. (its base is midnight of 1970 and not 1900).
Your subject says RBA/LRSN but RBA is not time derived and
cannot be converted to timestamp.
In Reply to Bharath Nunepalli:
I did the following to convert RBA/LRSN to Timestamp and
1. Executed the below SQL query to get the minutes and converted
that to microseconds
That will be 3686853600000000 micro seconds.
2. With LRSN=SUBSTR(D2X(MS), 1, 12), I got D192C7F2AB80
3. When I convert it back to Timestamp using BLSUXTOD, getting
10/30/2016 22:00:00.000000 but not 12/02/2016 22:00:00.000000
What did I do wrong here?