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
Question
Excel Convert Date To Number in Vertica
+1
+2Seems like the correct result is: 44267.4722222222
+1Ok thank you very much!!
+2You 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)
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
