如何调整日期转换相关的SQL查询语句以适配法英DD/MM/YYYY日期格式？

Hey there! Let's walk through how to fix this. You've got two numeric date columns (like 20211005 which translates to 5th October 2021) and you want to display them in the DD/MM/YYYY format, while keeping your date range filter working correctly. Here's what you need to know:

Critical First Note #

Never use formatted string dates for your WHERE clause comparisons! String sorting doesn’t match date logic (for example, 31/12/2021 would be treated as "larger" than 01/01/2022 as a string, which is totally wrong). Keep your filter logic using actual date types, and only format dates for display in your SELECT statement.

Adjusted Query by Database Type #

Below are tailored examples for the most common databases:

1. SQL Server

First convert the numeric value to a datetime (using style code 112 which aligns with the YYYYMMDD format), then format it to DD/MM/YYYY with FORMAT():

SELECT
  -- Format columns for display as DD/MM/YYYY
  FORMAT(CONVERT(DATETIME, CAST(H6_DATAINI AS VARCHAR(8)), 112), 'dd/MM/yyyy') AS H6_DATAINI_FORMATTED,
  FORMAT(CONVERT(DATETIME, CAST(H6_DATAFIN AS VARCHAR(8)), 112), 'dd/MM/yyyy') AS H6_DATAFIN_FORMATTED,
  -- Add your other columns here
FROM your_table_name
WHERE
  -- Keep filter logic using date types (your original condition is already valid here)
  CONVERT(DATETIME, CAST(H6_DATAINI AS VARCHAR(8)), 112) >= '${_from:date:iso}'
  AND CONVERT(DATETIME, CAST(H6_DATAFIN AS VARCHAR(8)), 112) <= '${_to:date:iso}'

2. MySQL

Use STR_TO_DATE() to turn the numeric string into a date, then DATE_FORMAT() to get the DD/MM/YYYY display:

SELECT
  DATE_FORMAT(STR_TO_DATE(H6_DATAINI, '%Y%m%d'), '%d/%m/%Y') AS H6_DATAINI_FORMATTED,
  DATE_FORMAT(STR_TO_DATE(H6_DATAFIN, '%Y%m%d'), '%d/%m/%Y') AS H6_DATAFIN_FORMATTED,
  -- Include other columns here
FROM your_table_name
WHERE
  STR_TO_DATE(H6_DATAINI, '%Y%m%d') >= '${_from:date:iso}'
  AND STR_TO_DATE(H6_DATAFIN, '%Y%m%d') <= '${_to:date:iso}'

3. Oracle

Use TO_DATE() to parse the numeric value into a date, then TO_CHAR() to format it:

SELECT
  TO_CHAR(TO_DATE(H6_DATAINI, 'YYYYMMDD'), 'DD/MM/YYYY') AS H6_DATAINI_FORMATTED,
  TO_CHAR(TO_DATE(H6_DATAFIN, 'YYYYMMDD'), 'DD/MM/YYYY') AS H6_DATAFIN_FORMATTED,
  -- Add other columns here
FROM your_table_name
WHERE
  TO_DATE(H6_DATAINI, 'YYYYMMDD') >= TO_DATE('${_from:date:iso}', 'YYYY-MM-DD')
  AND TO_DATE(H6_DATAFIN, 'YYYYMMDD') <= TO_DATE('${_to:date:iso}', 'YYYY-MM-DD')

Why This Works #

• The WHERE clause uses actual date type comparisons, so your date range filter will behave exactly as expected.
• The SELECT clause converts the raw numeric dates into the human-friendly DD/MM/YYYY format you need for display.

内容的提问来源于stack exchange，提问作者Ricky de Camargo