select distinct ((concat(trim(EXTRACT(month FROM(rqst.admt_dt))), '/01/', trim(EXTRACT(year FROM(rqst.admt_dt))))) as date format 'mm/dd/yyyy' end) as MonYr
from companydatabase.um_rqst rqst
The DD could be any day of a month but we are forcing it to the first of the month with the 01. Because of this force, when I create the volatile table the datatype ends up a varchar. I have attempted after the fact forcing it to be a date format but I get the 2665 error of invalid date. When I do a distinct on the MonYr, I see 1/01/2018 then 2/01/2018, etc. I thought maybe I could force the date format when I initially create the table but that is not working either. Any advice?
from companydatabase.um_rqst rqst
The DD could be any day of a month but we are forcing it to the first of the month with the 01. Because of this force, when I create the volatile table the datatype ends up a varchar. I have attempted after the fact forcing it to be a date format but I get the 2665 error of invalid date. When I do a distinct on the MonYr, I see 1/01/2018 then 2/01/2018, etc. I thought maybe I could force the date format when I initially create the table but that is not working either. Any advice?
