Db2 Date Format

DB2 Date format

SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYYMMDD')
FROM SYSIBM.SYSDUMMY1

Should work on both Mainframe and Linux/Unix/Windows DB2. Info Center entry for VARCHAR_FORMAT().

DATE FORMAT :DB2 _ SQL

Read DB2's manual:
https://www.ibm.com/docs/en/db2-for-zos/12?topic=sf-char

Using that docu:

I need to convert the non-standard in-format with a dash between day and hour, and dots as hour/minute and minute/second separators to a timestamp, using TO_TIMESTAMP().
From the obtained timestamp, I can use TO_CHAR() with a format string to give me back a string with the format I desire.

WITH
indata(ts) AS (
  SELECT 
    TO_TIMESTAMP(
     '2018-03-26-08.30.00.000000','YYYY-MM-DD-HH24.MI.SS.US'
    ) 
  FROM sysibm.sysdummy1
)
SELECT TO_CHAR(ts,'YYYY-MM-DD-HH24') AS new_format FROM indata;
  new_format    
---------------
 2018-03-26-08

Formatting date in YYYYMM format in DB2

If you're actually getting a DATE data type (and even if you're not, in the ISO format you have there, DB2 should be smart enough to convert it automatically), then you can use the VARCHAR_FORMAT scalar function:

SELECT VARCHAR_FORMAT(VALUE, 'YYYYMM')
FROM MYSCHEMA.MYTABLE
WHERE CODE = 'MYDATE'

convert string date(yyyy/mm/dd) to date format in db2

If your DB2 version is new enough, use to_date: select DATE(TO_DATE(CRDTR2, 'YYYY/MM/DD')) from ...

How to convert db2 date to YYYMMDD in android

The date string, 1200703 is in CYYMMDD format. This format was (I'm not sure if it is still in use as the last time when I used DB2 was in 2008) used by DB2.

In order to calculate the year, you need to use the following formula:

Year = 100 * C + 1900 + YY e.g. for CYY = 120, the value of year = 100 * 1 + 1900 + 20 = 2020.

Once you convert the CYY part into yyyy format, you can use date-time formatting API as shown below:

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Date;

public class Main {
    public static void main(String args[]) {
        // Given date string
        String dateStr = "1200703";

        // Convert the given date string into yyyyMMdd format
        int c = Integer.parseInt(dateStr.substring(0, 1));
        int yy = Integer.parseInt(dateStr.substring(1, 3));
        int year = 100 * c + 1900 + yy;
        String dateStrConverted = String.valueOf(year) + dateStr.substring(3);

        // ########## For Java 8 onwards ##############

        // Define a formatter
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyyMMdd");
        LocalDate localDate = LocalDate.parse(dateStrConverted, dtf);
        System.out.println("Default format: " + localDate);

        // Printing the date in a sample custom format
        DateTimeFormatter dtf1 = DateTimeFormatter.ofPattern("EEE MMM dd yyyy");
        String strDate1 = dtf1.format(localDate);
        System.out.println(strDate1);

        // ############################################

        // ############## Before Java 8 ###############

        // Define a formatter
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        Date utilDate = null;
        try {
            utilDate = sdf.parse(dateStrConverted);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        System.out.println("Default format: " + utilDate);

        // Printing the date in a sample custom format
        SimpleDateFormat sdf1 = new SimpleDateFormat("EEE MMM dd yyyy");
        String strDate2 = sdf1.format(utilDate);
        System.out.println(strDate2);

        // ############################################
    }
}

Output:

Default format: 2020-07-03
Fri Jul 03 2020
Default format: Fri Jul 03 00:00:00 BST 2020
Fri Jul 03 2020

Note: I recommend you use the modern date-time API. If the Android version which you are using is not compatible with Java-8, I suggest you backport using ThreeTen-Backport library. However, if you want to use the legacy API, you can use do so as shown in the answer.

Comparing dates in DB2

I'd be willing to bet that your tables have some invalid data...

A month of 0 for instance, or >= 13.

Another likely error is MM/DD of 02/30 or 02/31, or even 02/29 with a non-leap year.

You best bet is to define a User Defined Function (UDF) to build the dates for you, that way you can return NULL for invalid dates.

Optionally, you could check for specific invalid dates and return a valid value for your use case. For example, 02/30 ==> 02/28 or 02/29 if in a leap year.

I don't have any code example handy, but if I get some time I'll put something together and edit this answer.

UDF Example

CREATE OR REPLACE FUNCTION DateBuilder (
            mm INTEGER, dd INTEGER, yyyy INTEGER
    )
    RETURNS DATE
    LANGUAGE SQL
    NO EXTERNAL ACTION
    DETERMINISTIC
    RETURNS NULL ON NULL INPUT
    BEGIN
        DECLARE invalidDate CONDITION FOR '22007';
        DECLARE EXIT HANDLER for invalidDate return NULL;
        return to_date(char(yyyy) concat '-' concat char(mm) concat '-' concat char(dd), 'YYYY-MM-DD');
    END;