Community Conundrum 25: Feature Visualization is now live! Read More

Understand datePart formula

Level 3
Understand datePart formula

Hi - I am seeing something odd in my prep recipe and wondering what I am missing.


I have a date and would like to take just the day piece of it. So for 2020-06-13 I would expect the day to be 13. When I use datepart(mydate,'days') I get 12. 


What is even odder is that if my date (i.e. 2019-01-01T00:00:00) is the first of the month it returns 31.

For instance datePart(asDate('2020-01-01','yyyy-MM-dd'),'days') = 31 and not 1.

I tried datePart(now(),'days') and it correctly gave me 27 for today.

Is there anyway to get the actual day for a date or does anyone know why this is giving me odd results? Is there a problem with there not being a value for time?

Thanks for all the help in advance!

0 Kudos
3 Replies
Level 6


My guess is that we are having a time zone problem.


produces a date like 2020-08-27T14:33:48.244Z This is in Zulu or GMT time zone.  Note that the local time for me is actually 10:33am EDT. Eastern Daylight Saving Time is actually GMT -4 hours.

On my system


produces 2020-01-01T00:00:00.000Z

Note that this is in GMT +/- 0

In looking at the documentation I note for the function datePart the

Returned parts are always in local timezone and english.

For me


also produces 31 


produces 12

for me because this GMT time is actually on December 31st my Local Time.  (Eastern Standard Time)

On January 1 of any year I am GMT -5.  I can get the correct answer by entering.

datePart(inc(asDate('2020-01-01','yyyy-MM-dd'), 5,'hours'),'month')

Which adds 5 hours to the date '2020-01-01' producing


This time is actually the First of January 2020 in my time zone.

However, this does not seem like a great solution, because it does not work when we go from EDT to EST and back again.  My offset from GMT is either -4 or -5 depending on the time of year.

I was able to get better results by doing the following.

datePart(asDate('2020-07-01 EST','yyyy-MM-dd z'),'days')

 Where "EST" is my base time zone here in the eastern USA.

and "z" is the parser for time zone in asDate function

I know that this is not intuitive and I hope this is of some help.

Hay Dataiku staff is there a better way to do this now?  In the future is there anything that can be done to make Dates and Time more intuitive in DSS.


0 Kudos
Level 3

Hi - thank you for the above - I thought maybe it was something to do with the time zone. I decided to not use the formula and go with the following: 


This seems to work no matter what the time is and will give the day of the date.

0 Kudos
Level 6

Yes,  that is true as long as your strings are of a stable format.

There is always more than one way to solve a problem.

0 Kudos