CODEWITHSUNDEEP
sqlselectibm-midrange
macunte
macunte

Reputation: 465

AS400 SQL query for mm/yyyy from date field

For reporting purposes, I would like to retrieve my date(digits(mydate7)) field as mm/yyyy.

I have tried month(date(digits(mydate7)) + '/' + year (date(digits(mydate7)) as mmyyyy but get null as result. I can test with month(date(digits(mydate7)) and the year function individually and get a return set of data but not combined in field.

Upvotes: 0

Views: 5096

Answers (3)

lamLam
lamLam

Reputation: 475

Normally i do like this 

substr(char(mydate7,ISO),6,2) || '/' || substr(char(mydate7,ISO),1,4)

Upvotes: 0

James Allman
James Allman

Reputation: 41168

DB2/400 uses the double pipe character for concatenation:

RTRIM(CHAR(MONTH(DATE(DIGITS(mydate7))))) || '/' || RTRIM(CHAR(YEAR(DATE(DIGITS(mydate7)))))

Assuming mydate7 is in the format yyyyddd you could simplify it as:

RTRIM(CHAR(MONTH(DATE(DIGITS(mydate7))))) || '/' || SUBSTR(DIGITS(mydate7),1,4)

Simplified further with implicit casting:

RTRIM(MONTH(DIGITS(mydate7))) || '/' || RTRIM(mydate7/1000)

See the concatenation operator for more information.

Upvotes: 3

lc.
lc.

Reputation: 116458

Looks like CONCAT is the string concatenation function, not +. It's probably trying to add a numeric with a string and barfing.

Try CONCAT(CONCAT(month(date(digits(mydate7)),'/'), year(date(digits(mydate7))).

Upvotes: 1

Related Questions