Excel Convert Date To Number in Vertica

relireli Vertica Customer

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

Best Answer

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited April 2021 Answer ✓

    Seems like the correct result is: 44267.4722222222


  • Options
    relireli Vertica Customer

    Ok thank you very much!!

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator

    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;
    verticademos=> SELECT dt_2_num('2021-03-14 11:20:00');
    (1 row)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file