Getting the rightmost characters in a field in SQL without RIGHT ()

Question

I am working on an old AS400 that does not support the SQL RIGHT () function, and I need SELECT strings based on the X rightmost characters of a number that can contain 7 or 8 characters using SQL.

How can I get what I want if I can’t use RIGHT and I don’t know the exact length of the number.

here is some context in the random case this is useful:

A number is a date, but is stored as a number, so the first zero is deleted by the database, as a result of which dates starting from zero, like 01032016 [read as 01-03-2016], will be truncated to 1032016).

the database is quite large, so querying all rows without filtering this field consumes quite a lot of resources and time.

Changing the format of the dates in the database to something more reasonable will result in breaking software changes that I don’t support, and this is critical.

Answer 1

Does it support modulo?

Entrance: 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

Answer 2

SUBSTRING(mystr,character_length(mystr)-x+1,x)

or

SUBSTRING(mystr,character_length(mystr)-x+1)

Answer 3

, . , , .

, ( ), DIGITS() , , SUBSTR()

Answer 4

, , , , YYYYMMDD. , , , .

, , , - , . (150 000 400 .) , , DATE, , ( 8- , MMDDYYYY DDMMYYYY). , SQL, , ( , SQL).

Answer 5

While a substring (digits (date_column), x, y) is an option, another option is a combination of the MOD function and division. For example, to get the year, use this: SELECT MOD(date_column,10000)to get your day, use this: CAST(MOD(date_column,1000000)/10000 AS INT): use it, and to get a month CAST(date_column/1000000 AS INT).