Finding previous month start and end dates in Datacom SQL
Article ID: 10738
Updated On:
Products
Issue/Introduction
SQL query logic to process dates can be confusing at times, especially since the date can be stored in the database using a number of different formats. For example, an SQL Date type field is very different from a group of character fields containing the century, year, month, and day. As a result, trying to select records from the database using this predicate test (in the WHERE clause) requires different methods.
For the purpose of this article and to test the SQL queries listed here, you can create a small table of data. The code below will define the table and load the data used for the queries.
Here is an example of the table definition used in the DBSQLPR program:
CREATE TABLE SYSUSR.DT1 (RECID CHAR (4),
D1_DATE DATE,
D2_CHAR8 CHAR(8),
D3_NUM8 NUMERIC(8),
D4_CENTURY CHAR(2),
D4_YEAR CHAR(2),
D4_MONTH CHAR(2),
D4_DAY CHAR(2));
Next, here is the data to be loaded into this table.
INSERT INTO SYSUSR.DT1 VALUES('D001','2016-07-01','20160701',20160701,'20','16','07','01');
INSERT INTO SYSUSR.DT1 VALUES('D002','2016-07-26','20160726',20160726,'20','16','07','26');
INSERT INTO SYSUSR.DT1 VALUES('D003','2016-07-27','20160727',20160727,'20','16','07','27');
INSERT INTO SYSUSR.DT1 VALUES('D004','2016-07-28','20160728',20160728,'20','16','07','28');
INSERT INTO SYSUSR.DT1 VALUES('D005','2016-07-29','20160729',20160729,'20','16','07','29');
INSERT INTO SYSUSR.DT1 VALUES('D006','2016-07-30','20160730',20160730,'20','16','07','30');
INSERT INTO SYSUSR.DT1 VALUES('D007','2016-07-31','20160731',20160731,'20','16','07','31');
INSERT INTO SYSUSR.DT1 VALUES('D008','2016-08-01','20160801',20160801,'20','16','08','01');
INSERT INTO SYSUSR.DT1 VALUES('D009','2016-08-02','20160802',20160802,'20','16','08','02');
INSERT INTO SYSUSR.DT1 VALUES('D010','2016-08-03','20160803',20160803,'20','16','08','03');
INSERT INTO SYSUSR.DT1 VALUES('D011','2016-08-31','20160831',20160831,'20','16','08','31');
INSERT INTO SYSUSR.DT1 VALUES('D012','2017-06-05','20170605',20170605,'20','17','06','05');
INSERT INTO SYSUSR.DT1 VALUES('D013','2017-06-06','20170606',20170606,'20','17','06','06');
INSERT INTO SYSUSR.DT1 VALUES('D014','2017-06-07','20170607',20170607,'20','17','06','07');
INSERT INTO SYSUSR.DT1 VALUES('D015','2017-06-08','20170608',20170608,'20','17','06','08');
INSERT INTO SYSUSR.DT1 VALUES('D016','2017-06-30','20170630',20170630,'20','17','06','30');
INSERT INTO SYSUSR.DT1 VALUES('D017','2017-07-01','20170701',20170701,'20','17','07','01');
INSERT INTO SYSUSR.DT1 VALUES('D018','2017-07-15','20170715',20170715,'20','17','07','15');
INSERT INTO SYSUSR.DT1 VALUES('D019','2017-07-26','20170726',20170726,'20','17','07','26');
INSERT INTO SYSUSR.DT1 VALUES('D020','2017-07-27','20170727',20170727,'20','17','07','27');
INSERT INTO SYSUSR.DT1 VALUES('D021','2017-07-28','20170728',20170728,'20','17','07','28');
INSERT INTO SYSUSR.DT1 VALUES('D022','2017-07-29','20170729',20170729,'20','17','07','29');
INSERT INTO SYSUSR.DT1 VALUES('D023','2017-07-30','20170730',20170730,'20','17','07','30');
INSERT INTO SYSUSR.DT1 VALUES('D024','2017-07-31','20170731',20170731,'20','17','07','31');
INSERT INTO SYSUSR.DT1 VALUES('D025','2017-08-01','20170801',20170801,'20','17','08','01');
INSERT INTO SYSUSR.DT1 VALUES('D026','2017-08-02','20170802',20170802,'20','17','08','02');
INSERT INTO SYSUSR.DT1 VALUES('D027','2017-08-03','20170803',20170803,'20','17','08','03');
INSERT INTO SYSUSR.DT1 VALUES('D028','2017-08-04','20170804',20170804,'20','17','08','04');
INSERT INTO SYSUSR.DT1 VALUES('D029','2017-08-05','20170805',20170805,'20','17','08','05');
INSERT INTO SYSUSR.DT1 VALUES('D030','2017-08-06','20170806',20170806,'20','17','08','06');
INSERT INTO SYSUSR.DT1 VALUES('D031','2017-08-07','20170807',20170807,'20','17','08','07');
INSERT INTO SYSUSR.DT1 VALUES('D032','2017-08-08','20170808',20170808,'20','17','08','08');
INSERT INTO SYSUSR.DT1 VALUES('D033','2017-08-09','20170809',20170809,'20','17','08','09');
INSERT INTO SYSUSR.DT1 VALUES('D034','2017-08-10','20170810',20170810,'20','17','08','10');
INSERT INTO SYSUSR.DT1 VALUES('D035','2017-08-15','20170815',20170815,'20','17','08','15');
INSERT INTO SYSUSR.DT1 VALUES('D036','2017-08-20','20170820',20170820,'20','17','08','20');
INSERT INTO SYSUSR.DT1 VALUES('D037','2017-08-31','20170831',20170831,'20','17','08','31');
Resolution
This article is focused primarily on using the month prior to the desired month, and selecting records based on the date being sometime that month, i.e., with a date greater than or equal to the first day of the month, and less than or equal to the last day of the month. In this example, the "CURRENT DATE" is assumed to be 9 August 2017. To find the first day of the prior month, first get the first day of the desired month, and then subtract 1 month. For the purpose of this example:
- Start with CURRENT DATE (20170809)
- Subtract the current day value, minus one (08 - 1) to get the first day of this month (20170801)
- Subtract one month (20170701)
Here is the SQL code to get this result:
DATE(CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH)
Now, to find the last day of the prior month, we want to get to the first day of the current month, and then go back one more day. This logic is a little simpler, since we start with the current date and subtract the current day value (which would result in the day zero of the month, or the last day of the prior month). Here is that SQL code:
DATE(CURRENT DATE - (DAY(CURRENT DATE)) DAYS)
Now that we know what we have to work with for the desired prior month values, we can look at the different database fields to do the comparison. There are four different types of columns discussed in this article.
1. A column that has the SQL Date data type. This is data that is stored internally as a Date format, and will be able to compare directly to the results of our DATE() function above.
2. A column defined as Character(8). Most columns that use this format will have the date stored as YYYYMMDD, representing COBOL-defined data of PIC 9(08) or PIC X(08). To work with this data, SQL needs to understand the date format, and this is done by converting YYYYMMDD to YYYY-MM-DD. In order to achieve this format, we need to take substrings of this column and concatenate them with the dash. Using the above definition, this code would look like this:
SUBSTR(D2_CHAR8,1,4) || '-' ||
SUBSTR(D2_CHAR8,5,2) || '-' ||
SUBSTR(D2_CHAR8,7,2)
3. A column defined as Numeric(8). Again, we need to define the date format with dashes, as YYYY-MM-DD. However, because SQL knows this column is numeric due to our earlier table definition, it cannot simply be built as the above character column. In order to work with this column, we need to first convert the Numeric data to Integer format, which results in a 10-digit, leading-zero-filled number. We need to then convert it to Character format using the DIGITS function, and then use substrings as above on this 10-character field. Here is an example:
SUBSTR(DIGITS(INTEGER(D3_NUM8)),3,4) || '-' ||
SUBSTR(DIGITS(INTEGER(D3_NUM8)),7,2) || '-' ||
SUBSTR(DIGITS(INTEGER(D3_NUM8)),9,2)
4. The last format we will look at here involves a group format, where multiple columns are used to hold the date. One example is with a group of the century (CC), year (YY), month (MM), and day (DD). Because these are separate columns, we need to only concatenate them in the correct order, adding the dashes, to give us the desired YYYY-MM-DD or CCYY-MM-DD format. Here is an example, using Character-defined columns - if they are numeric, you will need to use a construct like the above, with DIGITS and INTEGER, to produce character-format values first.
D4_CENTURY ||
D4_YEAR || '-' ||
D4_MONTH || '-' ||
D4_DAY
Putting all this together, we have the sample SQL queries below to select records with the various format date values that fall within the prior month. You can also download this SQL source from the attachment below.
//SYSIN DD *
-- .5...10....5...20....5...30....5...40....5...50....5...60....5...70..
-- ********************************************************************
-- * THESE EXAMPLES SHOW HOW TO TEST A SINGLE OR GROUP OF COLUMNS
-- * THAT CONTAIN SOME FORM OF DATE VALUE
-- * AGAINST THE PREVIOUS MONTH (FROM FIRST DAY TO LAST DAY)
-- *
-- * THE FORMULA FOR FINDING THE FIRST DAY OF THE PREVIOUS MONTH IS
-- * START WITH THE CURRENT DATE,
-- * SUBTRACT (CURRENT DAY VALUE - 1) DAY TO GET THIS MONTH DAY 1,
-- * SUBTRACT 1 MONTH
-- * FOR EXAMPLE, IF THE CURRENT DATE IS 9 AUGUST 2017,
-- * START WITH 20170809
-- * SUBTRACT (CURRENT DAY -1) DAY (8 DAYS) = 20170801
-- * SUBTRACT 1 MONTH = 20170701
-- * THIS FORMULA IN SQL CODE LOOKS LIKE THIS:
-- * DATE(CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH)
-- *
-- * THE FORMULA FOR FINDING THE LAST DAY OF THE PREVIOUS MONTH IS
-- * START WITH THE CURRENT DATE,
-- * SUBTRACT (CURRENT DAY VALUE - 1) DAY TO GET THIS MONTH DAY 1,
-- * SUBTRACT 1 DAY
-- * FOR EXAMPLE, IF THE CURRENT DATE IS 9 AUGUST 2017,
-- * START WITH 20170809
-- * SUBTRACT (CURRENT DAY -1) DAY (8 DAYS) = 20170801
-- * SUBTRACT 1 DAY = 20170731
-- * THIS FORMULA IN SQL CODE LOOKS LIKE THIS:
-- * DATE(CURRENT DATE - (DAY(CURRENT DATE)) DAYS)
-- ********************************************************************
--
-- ********************************************************************
-- *1. USING A COLUMN ALREADY DEFINED AS A DATE TYPE
-- ********************************************************************
SELECT * FROM SYSUSR.DT1
WHERE D1_DATE
>= DATE(CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH)
AND D1_DATE
<= DATE(CURRENT DATE - (DAY(CURRENT DATE)) DAYS)
;
-- ********************************************************************
-- *2. USING A COLUMN DEFINED AS CHARACTER(8) ('YYYYMMDD')
-- * IN ORDER TO USE THE DATE FUNCTION, THE DATA MUST HAVE A
-- * RECOGNIZABLE DATE FORMAT. HERE WE CREATE YYYY-MM-DD BY ADDING
-- * THE '-' INTO THE VALUE AFTER CAPTURING A SUBSTRING FOR THE
-- * YEAR, MONTH, AND DAY.
-- ********************************************************************
SELECT * FROM SYSUSR.DT1
WHERE SUBSTR(D2_CHAR8,1,4) || '-' ||
SUBSTR(D2_CHAR8,5,2) || '-' ||
SUBSTR(D2_CHAR8,7,2)
>= DATE(CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH)
AND SUBSTR(D2_CHAR8,1,4) || '-' ||
SUBSTR(D2_CHAR8,5,2) || '-' ||
SUBSTR(D2_CHAR8,7,2)
<= DATE(CURRENT DATE - (DAY(CURRENT DATE)) DAYS)
;
-- ********************************************************************
-- *3. USING A COLUMN DEFINED AS NUMERIC(8) (YYYYMMDD)
-- * AS ABOVE, WE NEED TO INSERT '-' TO MAKE A RECOGNIZABLE DATE.
-- * HOWEVER, IN ORDER TO TREAT A NUMERIC COLUMN AS A STRING TO USE
-- * A SUBSTRING FUNCTION, WE FIRST NEED TO CONVERT THE NUMERIC
-- * COLUMN INTO AN INTEGER (WHICH WILL HAVE 10 DIGITS AND BE IN THE
-- * FORMAT 00YYYYMMDD, AND THEN USE THE APPROPRIATE SUBSTRING.
-- ********************************************************************
SELECT * FROM SYSUSR.DT1
WHERE SUBSTR(DIGITS(INTEGER(D3_NUM8)),3,4) || '-' ||
SUBSTR(DIGITS(INTEGER(D3_NUM8)),7,2) || '-' ||
SUBSTR(DIGITS(INTEGER(D3_NUM8)),9,2)
>= DATE(CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH)
AND SUBSTR(DIGITS(INTEGER(D3_NUM8)),3,4) || '-' ||
SUBSTR(DIGITS(INTEGER(D3_NUM8)),7,2) || '-' ||
SUBSTR(DIGITS(INTEGER(D3_NUM8)),9,2)
<= DATE(CURRENT DATE - (DAY(CURRENT DATE)) DAYS)
;
-- ********************************************************************
-- *4. USING SEPARATE CHARACTER COLUMNS FOR CENTURY, YEAR, MONTH, DAY
-- * AS ABOVE, WE NEED TO INSERT '-' TO MAKE A RECOGNIZABLE DATE.
-- * HERE, WE HAVE SEPARATE COLUMNS, SO WE ONLY NEED TO JOIN THEM
-- * TOGETHER IN THE CORRECT ORDER TO GIVE US THE DESIRED FORMAT.
-- ********************************************************************
SELECT * FROM SYSUSR.DT1
WHERE DATE(D4_CENTURY ||
D4_YEAR || '-' ||
D4_MONTH || '-' ||
D4_DAY)
>= DATE(CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH)
AND DATE(D4_CENTURY ||
D4_YEAR || '-' ||
D4_MONTH || '-' ||
D4_DAY)
<= DATE(CURRENT DATE - (DAY(CURRENT DATE)) DAYS)
;
/*
Finally, to test a date against the second prior month, you would use the same calculations as above, except you would subtract another month. This makes the "first day" test:
DATE(CURRENT DATE - (DAY(CURRENT DATE)-1) DAYS - 2 MONTH)
And the "last day" test:
DATE(CURRENT DATE - (DAY(CURRENT DATE)) DAYS - 1 MONTH)
Additional Information
For more information about the SQL functions, please refer to the following documentation topics:
Arithmetic Operations for Dates, Times, and Timestamps and also
Scalar Functions
Other Knowledge Base articles you might find useful are:
- Knowledge Base article 212993, titled "Using the Datacom/DB SQL function TIMESTAMPDIFF to compute dates with rounded results"
- Knowledge Base article 231960, titled "Getting the number of seconds since midnight using SQL"
As always, please contact Broadcom support for Datacom if you have further questions.
Attachments
Feedback
