Day of Year in Redshift

Reply
Highlighted
Major Blue Belt

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


Accepted Solutions
Highlighted
Black Belt

Re: Day of Year in Redshift

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.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post


All Replies
Highlighted
Major Red Belt

Re: Day of Year in Redshift

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.


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Highlighted
Major Blue Belt

Re: Day of Year in Redshift

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.

Highlighted
Black Belt

Re: Day of Year in Redshift

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.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post

Highlighted
Major Blue Belt

Re: Day of Year in Redshift

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.

Announcements
Stand out in the Dojo: Choose your own recognizable username and add a unique profile photo in your profile settings.