IBM Support

Can I use data type, DATE with the SUBSTR function?

Question & Answer


Question

I have a query which begins with a date on a row and adds a month up to a variably occurring number, for example, 1991-11-05 and the query adds a month to this date until it reaches a certain number of occurrences. The result I end up with is 1991-11-05, 1991-12-05, 1992-01-05, 1992-02-05, etc. What I would like is just the year and month of this date in a month/year format. In general, my query for this date is as follows: (START_DTE -1 MONTH) + FACTOR_MONTHS where FACTOR MONTHS are from 1 to nnn. I tried using SUBSTR, but I continually get a SQL error of: Argument '1' of scalar function 'SUBSTR' is invalid. This error occurs because I tried to use SUBSTR as follows: SUBSTR(((START_DTE -1 MONTH) + FACTOR_MONTHS),1,7)

[{"Product":{"code":"SS9UMF","label":"DB2 Query Management Facility"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"QMF for TSO\/CICS","Platform":[{"code":"PF025","label":"Platform Independent"},{"code":"PF032","label":"VM"},{"code":"PF038","label":"z\/VSE"}],"Version":"3.3;6.1;7.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Log InLog in to view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Document Information

Modified date:
01 May 2025

UID

swg21006696

Page Feedback