How can I convert a date format in SQL?

Question

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

Answer 1

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)

Answer 2

You can use:

convert(varchar,[date_you_want_to_convert],112)

As shown in microsoft sql docs

Answer 3

Bro I think you already have the correct format to use it into a query to filter records, see like I do a query using your date format.

This is my query

SELECT * FROM program_authorization_data WHERE dateCreate between '2022-01-13' AND '2022-01-15';