2

I need to get the day of the week of a date in H2 as a number. There is a function DAY_OF_WEEK(date) which returns Sunday as the first day of the week. However, I need Monday to be the first day of the week. Does someone know a function in H2 to accomplish this?

I need it for a "weekday" function in a custom hibernate dialect. The values returned shoud correspond to the values of the java DayOfWeek enum.

In MySQL I solved it like this: 

registerFunction("weekday", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "WEEKDAY(?1)+1"));

Now I don't know how to accomplish this with H2. Thanks in advance for any help!

maria vill
  • 1,014
  • 1
  • 7
  • 11

1 Answers1

1

You already found the H2 function DAY_OF_WEEK (dateAndTime). Which is defined in the h2 function documentation and returns 1 as Sunday.

DAY_OF_WEEK ( dateAndTime )
Returns the day of the week (1 means Sunday).
Example:
DAY_OF_WEEK(CREATED)

You can use ISO_DAY_OF_WEEK ( dateAndTime ) if you want that 1 is mapped to Monday. Which is defined as follows in the documentation.

ISO_DAY_OF_WEEK ( dateAndTime )
Returns the ISO day of the week (1 means Monday).
Example:
ISO_DAY_OF_WEEK(CREATED)

I hope that helps! :)

Bernhard
  • 703
  • 5
  • 25