Day of Year in Redshift

Does Domo's Redshift support day of year?

 

I'm trying to parse my date column using the formulas and none work:

 

date_part(doy, "date") as "day of year"

date_part(dy, "date") as "day of year"

date_part(yearday, "date") as "day of year"

date_part(dayofyear, "date") as "day of year"

 

In the Redshift documentation this should work: https://docs.aws.amazon.com/redshift/latest/dg/r_Dateparts_for_datetime_functions.html

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤
    Accepted Answer

    Another date function you can use in Redshift is Extract. I use it and it looks like this:

    extract(year from "dateofevent")  DateofEventYear,

    extract(day from "dateofevent") DayofEvent,

    I am able to use the date_part function as @jaeW_at_Onyx suggests, so I'm not sure what might be going on with that function in your instance, but maybe you will have better luck with the Extract function.

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    What error message did you get?  This works fine.

     

    select date_part(dayofyear, cast('2020-02-14' as date))

     

     

    Is your Date column a date or text?

     

    Domo processes Redshift ETL in a Redshift cluster.  So if you're throwing an error during transform steps it's usually a code issue.

  • select *,
    date_part(y,"date") as "year",
    (case when date_part(q,"date") <= 2 then 1 else 2 end) as "half year",
    date_part(q,"date") as "quarter",
    date_part(m,"date") as "month",
    date_part(dayofyear, "date") as "day of year"
    from "group_by"

     

    Date is a date field, not text. It wasn't throwing an error for any of the other date_part functions in this query.

  • The database reported a syntax error: [Amazon](500310) Invalid operation: Invalid datetime part in DATE_PART(). Details: ----------------------------------------------- error: Invalid datetime part in DATE_PART(). code: 8001 context: query: 8206522 location: datetime

     

    That's the error I'm getting. it's from the dayofyear line of the query.

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!