Need to convert a short date in format YYYYMMdd to epochDay

SQL++
1

Hi ,

I need to convert a short date in fromat (yyyyMMdd) into epochDay

Can any one help me with this ?

Eg: 20222011[Short date in yyyyMMdd] into → 19276 [epochDay]

2

With version 7 (I used 7.1.0):

SELECT str_to_millis('20221020+00','YYYYMMDDTZD');

will give you the UTC Epoch/Unix milliseconds; divide by (24*60*60*1000) for Epoch days. I note using an explicit time zone to make sure there is no drift once purely a number (presumably for external consumption).

You can of course use string concatenation to append “+00” to your short-date values. e.g.

cbq> SELECT str_to_millis(t.d||'+00','YYYYMMDDTZD')/(24*60*60*1000) FROM {"d":"20221011"} t;
{
...
    "results": [
    {
        "$1": 19276
...

Another option is to round:

SELECT ROUND(str_to_millis(t.d,'YYYYMMDD')/(24*60*60*1000)) FROM {"d":"20221011"} t;

which should also take care of time zone impact on the millisecond value.

HTH.

1 Like
3

@dh Thank you. This is exactly what Im lookin for.