i'm working on an old AS400 that does not provide support for the SQL RIGHT() function, and i need to SELECT rows based on the X rightmost characters of a number that might be either 7 or 8 characters long using SQL.
How can i get what i want if i cant use RIGHT and i don't know the exact length of the number?.
here is some context in the off chance it's usefull:
The number is a date but was stored as a number, so the first zero is stripped away by the database, causing dates starting with zero like 01032016 [read as 01-03-2016] to be trimmed to 1032016).
the database is quite big so requesting all the rows without filtering this field consumes quite a bit of resources and time.
Changing the format of the dates in the database to something more reasonable would cause breaking changes on software I dont maintain and that is mission critial.
Does it support modulo?
Input: 1032016
// Outputs: 2016
YOURDATEASINT % 10000 AS Year
MOD(YOURDATEASINT, 10000) AS Year
// Outputs: 32016
YOURDATEASINT % 1000000 AS MonthYear
MOD(YOURDATEASINT, 1000000) AS MonthYear
// Outputs: 1032016
YOURDATEASINT % 100000000 AS DayMonthYear
MOD(YOURDATEASINT, 100000000) AS DayMonthYear