Search code examples
sqldateformatdb2-400ibm-data-studio

How can I convert a date format in SQL?

Some recently helped me to find a date with the command shown below, it returns the date in YYYY-MM-DD format, how can I convert that to YYYYMMDD so that I can use it to filter records in my where clause?

DATE(YEAR(CURRENT_DATE)||'-'||MONTH(CURRENT_DATE)||'-1') - 1 MONTH
Solution

  • You should tag with db2-400 when you ask questions about SQL running on OS400/IBMi/...

    If you use a recent version of the OS you can get what you want with

    dec(your_date)

    And with a recent version of the OS with some recent TR installed you can get first day of preceding month as a decimal using :

    dec(current date - (day(current date) - 1) day - 1 month)

    or

    date(trunc_timestamp(current date, 'MM') - 1 month)