The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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:

Best Answer

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

    Seems like the correct result is: 44267.4722222222


  • relireli Vertica Customer

    Ok thank you very much!!

  • 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