I quickly needed to check the monthly progress of a certain variable over time. Problem was that those bookings/incomes are spread over the month, sometimes only one booking line, sometimes two... So I needed a grouping variable, that the "absolute month", i.e. the usual year-month value,that you know from financial system, e.g. 201709 for this current month.
Here's the easy way to put that into a function on DB2:
create function yearmonth(TS date) returns varchar(6) no external action deterministic return rtrim(char(year(TS))) || right(digits(month(TS)),2)
Then you can easily do a group by yearmonth(date).
(Don't omit the "not external action" and "deterministic" parts, because a) they are true and b) they are needed for grouping)
Of course you don't have to create a function for this, but easier then re-typing it, or creating a view.
(You might want to create the same function for timestamp as well... just so happened that I now only needed it with the date signature).
You're welcome :)
Here's the easy way to put that into a function on DB2:
create function yearmonth(TS date) returns varchar(6) no external action deterministic return rtrim(char(year(TS))) || right(digits(month(TS)),2)
Then you can easily do a group by yearmonth(date).
(Don't omit the "not external action" and "deterministic" parts, because a) they are true and b) they are needed for grouping)
Of course you don't have to create a function for this, but easier then re-typing it, or creating a view.
(You might want to create the same function for timestamp as well... just so happened that I now only needed it with the date signature).
You're welcome :)
Subscribe
No comments:
Post a Comment