DOW function

Ravindra_Gudime

Hi All - I am trying to a convert a function from Teradata to Vertica. I am having a challenge, can anyone take a stab at it. -------------------------------------------------------------------- --- MOD FUNCTION ---------------------------------------------------------------------- --Teradata Mod Function 0=Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4= Friday, 5= Saturday, 6= Sunday SELECT top 10 (chnl_szr_dt - DATE '1900-01-01') MOD 7, chnl_szr_dt - DATE '1900-01-01' , chnl_szr_dt, date, (date - (date-1)) mod 7, (DATE '2012-12-31' - date '2013-01-31') mod 7 FROM EDWCDRVIEWS.bl_stmnt_dtl_usge U -------------------------------------------------------------------- --- DOW FUNCTION (SAME AS MOD FUNCTION IN TERADATA) ---------------------------------------------------------------------- -- Vertica Dow Function' 0= Sunday, 1=Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5= Friday, 6= Saturday SELECT EXTRACT (DOW FROM (chnl_szr_dt)) FROM tempdb.bl_stmnt_dtl_usge -- WORKS GOOD SELECT ((chnl_szr_dt) - (DATE '1900-01-01')) FROM tempdb.bl_stmnt_dtl_usge-- WORKS GOOD --This query does not work and throws a error function cataglo.date_part does not exist or permission is denied for catalog.date_part (unkown, int) SELECT EXTRACT (DOW FROM ( (chnl_szr_dt) - (DATE '1900-01-01'))) FROM tempdb.bl_stmnt_dtl_usge

Ravindra_Gudime

--The following will work --Thanks to Sampath Rudravaram SELECT MOD ( ( (chnl_szr_dt) - (DATE '1900-01-01') ), 7) AS date1 FROM tempdb.bl_stmnt_dtl_usge SELECT chnl_szr_dt, MOD ( ( (chnl_szr_dt) - (DATE '1900-01-01') ), 7), EXTRACT (DOW FROM (chnl_szr_dt)) FROM tempdb.bl_stmnt_dtl_usge