Excel Convert Date To Number in Vertica

reli

Hi all,
I need to convert the date to a number the same as the excel formula does.
for example, the cell with value: 2021-03-14 11:20:00 -> convert to number format the result is : 44269.472225625
if I do this in Vertica:
SELECT TIMESTAMPDIFF(SECOND ,'1900-01-01 00:00:00', '2021-03-14 11:20:00'::TIMESTAMP)/(86400)
I get the result 44267.4722222222
there is a better solution?
link to excel doc:https://www.myonlinetraininghub.com/excel-date-and-time

Jim_Knicely

Seems like the correct result is: 44267.4722222222

reli

Ok thank you very much!!

Jim_Knicely

You could create cool little User Defined SQL Function

verticademos=> CREATE OR REPLACE function dt_2_num(x TIMESTAMP) RETURN NUMERIC(18,9)
verticademos-> AS
verticademos-> BEGIN
verticademos->   RETURN TIMESTAMPDIFF(SECOND ,'1900-01-01 00:00:00', x) / 86400;
verticademos-> END;
CREATE FUNCTION

verticademos=> SELECT dt_2_num('2021-03-14 11:20:00');
    dt_2_num
-----------------
 44267.472222222
(1 row)