Convert datetime to real

[malargopal]

Hi All,

What could be the equvalant line for the following line

floor(CAST( DATEDIFF( DAY, "dly_partcpnt_dim"."brth_dt",convert(datetime, {d '2012-02-29'}) ) as REAL ) / 365.25).
Note :This is sql code . I want the same in vertica.

Please help me out .

Thanks in advance ,
Malar

[id10t]

Hi!

Code: Select all

select age_in_years(dly_partcpnt_dim.brth_dt, '2012-02-29');

Code: Select all

daniel=> select age_in_years(sysdate(), '1987-02-15');
 age_in_years 
--------------
           26
(1 row)

NOTE: be careful - order of dates is matter

Code: Select all

daniel=> select age_in_years('1987-02-15', sysdate());
 age_in_years 
--------------
          -27
(1 row)

[malargopal]

Hi ,

Thanks a lot.

But i need some clarification here.

in the line which i have posted contain floor,cast, convert , datediff.
Does ageinyears meet all the requirement . we have datediff and ageinyears as different functions in vertica.

pls explain.

Thanks,
Malar

[nnani]

Hi malargopal,

Below is the snippet that works fine at my end.

Code: Select all

select * from public.test;
 pm_id | pm_name  |   d_date
-------+----------+------------
     1 | abkd     |
     2 | lkdfg    |
     3 | bmnvc    |
     4 | akbndfak | 2012-11-22
    32 | alnalaf  | 2012-10-25
    32 | alnalaf  | 2011-10-02
    33 | afadf    | 2011-11-04
(7 rows)

select FLOOR(CAST( DATEDIFF( DAY, d_date,'2012-02-29')AS REAL) /365.25)  from public.test;
 FLOOR
-------



    -1
    -1
     0
     0
(7 rows)

I hope this is giving you the expected results