How to determine previous month start and end dates in SQL

Document ID:  TEC1659247
Last Modified Date:  08/09/2017
{{active ? 'Hide' : 'Show'}} Technical Document Details

Products

  • CA Datacom/DB

Releases

  • CA Datacom/DB:Release:14.0
  • CA Datacom/DB:Release:15.0
  • CA Datacom/DB:Release:15.1

Components

  • CA Datacom/AD:AD
  • CA DATACOM/DB:DB
  • CA Dataquery for CA Datacom:DQ
  • CA DATACOM SQL:SQL
Summary:

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.

Background:

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. It is also provided here for download. SQL_Date_Define1.txt

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));        

Then, here is the data that was loaded into this table. Note that the format of the INSERT statement is
INSERT INTO SYSUSR.DT1 VALUES(...)
but only the VALUES statements are shown here for brevity - you will need to add the INSERT above each line here.

VALUES('D001','2016-07-01','20160701',20160701,'20','16','07','01');
VALUES('D002','2016-07-26','20160726',20160726,'20','16','07','26');
VALUES('D003','2016-07-27','20160727',20160727,'20','16','07','27');
VALUES('D004','2016-07-28','20160728',20160728,'20','16','07','28');
VALUES('D005','2016-07-29','20160729',20160729,'20','16','07','29');
VALUES('D006','2016-07-30','20160730',20160730,'20','16','07','30');
VALUES('D007','2016-07-31','20160731',20160731,'20','16','07','31');
VALUES('D008','2016-08-01','20160801',20160801,'20','16','08','01');
VALUES('D009','2016-08-02','20160802',20160802,'20','16','08','02');
VALUES('D010','2016-08-03','20160803',20160803,'20','16','08','03');
VALUES('D011','2016-08-31','20160831',20160831,'20','16','08','31');
VALUES('D012','2017-06-05','20170605',20170605,'20','17','06','05');
VALUES('D013','2017-06-06','20170606',20170606,'20','17','06','06');
VALUES('D014','2017-06-07','20170607',20170607,'20','17','06','07');
VALUES('D015','2017-06-08','20170608',20170608,'20','17','06','08');
VALUES('D016','2017-06-30','20170630',20170630,'20','17','06','30');
VALUES('D017','2017-07-01','20170701',20170701,'20','17','07','01');
VALUES('D018','2017-07-15','20170715',20170715,'20','17','07','15');
VALUES('D019','2017-07-26','20170726',20170726,'20','17','07','26');
VALUES('D020','2017-07-27','20170727',20170727,'20','17','07','27');
VALUES('D021','2017-07-28','20170728',20170728,'20','17','07','28');
VALUES('D022','2017-07-29','20170729',20170729,'20','17','07','29');
VALUES('D023','2017-07-30','20170730',20170730,'20','17','07','30');
VALUES('D024','2017-07-31','20170731',20170731,'20','17','07','31');
VALUES('D025','2017-08-01','20170801',20170801,'20','17','08','01');
VALUES('D026','2017-08-02','20170802',20170802,'20','17','08','02');
VALUES('D027','2017-08-03','20170803',20170803,'20','17','08','03');
VALUES('D028','2017-08-04','20170804',20170804,'20','17','08','04');
VALUES('D029','2017-08-05','20170805',20170805,'20','17','08','05');
VALUES('D030','2017-08-06','20170806',20170806,'20','17','08','06');
VALUES('D031','2017-08-07','20170807',20170807,'20','17','08','07');
VALUES('D032','2017-08-08','20170808',20170808,'20','17','08','08');
VALUES('D033','2017-08-09','20170809',20170809,'20','17','08','09');
VALUES('D034','2017-08-10','20170810',20170810,'20','17','08','10');
VALUES('D035','2017-08-15','20170815',20170815,'20','17','08','15');
VALUES('D036','2017-08-20','20170820',20170820,'20','17','08','20');
VALUES('D037','2017-08-31','20170831',20170831,'20','17','08','31');

 

Instructions:

This article is focused primarily on using the month prior to the current 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. 

To find the first day of the prior month, we first get the first day of the current month, and then subtract 1 month. For example, if the current date is 9 August 2017, the below Date function will:

  1. Start with CURRENT DATE (20170809)
  2. Subtract the current day value, minus one (08 - 1) to get the first day of this month (20170801)
  3. 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 here. SQL_Date_Select1.txt

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

For version 15.1, in the CA Datacom Core DocOps webpage, please see
Reference SQL Reference / SQL Expressions / Arithmetic Operations for Dates, Times, and Timestamps and also 
Reference SQL Reference / SQL Functions / Scalar Functions

For version 15.0, in the CA Datacom Core DocOps webpage, please see
Reference / SQL Reference / SQL Expressions / Arithmetic Operations for Dates, Times, and Timestamps and also 
Reference SQL Reference / SQL Functions / Scalar Functions

For version 14.0, in the CA Datacom SQL User Guide, in the section
Expressions > Arithmetic Operations for Dates, Times and Timestamps and in
Functions > Scalar Functions

As always, please contact CA Technologies support for CA Datacom if you have further questions.

Please help us improve!

Will this information enable you to resolve your issue?

Please tell us what we can do better.

{{feedbackText.length ? feedbackText.length : '0'}}/255

{{status}}

Not what you were looking for?

Search Again >

Product Information

Support by Product >

Communities

Join a Community >