DBeaver中MM-dd-yy格式日期转换异常，如何修复以支持日期运算？

核心原因 #

CAST('02-13-22' AS DATE)的解析逻辑由数据库决定，和DBeaver的客户端显示格式无关。你的数据库可能默认不识别MM-dd-yy格式，把字符串'02-13-22'误解析为数值（比如2.1322），截断后得到02，导致无法进行日期运算。

解决步骤 #

1. 修正CTE语法错误
   你的代码中VALUES子句缺少闭合括号，会导致语法报错，先补上：

   WITH users (user_id, action, action_date) 
   AS (VALUES 
         (1, 'start', CAST('02-13-22' AS date)), 
         (1, 'cancel', CAST('02-14-22' AS date))
       ) -- 补上这个闭合括号
   SELECT * FROM users;
   

2. 使用显式日期转换函数
   不要依赖CAST的默认解析，根据你使用的数据库，用对应函数指定格式：

   • MySQL/MariaDB：用STR_TO_DATE指定格式

     WITH users (user_id, action, action_date) 
     AS (VALUES 
           (1, 'start', STR_TO_DATE('02-13-22', '%m-%d-%y')), 
           (1, 'cancel', STR_TO_DATE('02-14-22', '%m-%d-%y'))
         )
     SELECT * FROM users;
     

   • PostgreSQL：用TO_DATE指定格式

     WITH users (user_id, action, action_date) 
     AS (VALUES 
           (1, 'start', TO_DATE('02-13-22', 'MM-DD-YY')), 
           (1, 'cancel', TO_DATE('02-14-22', 'MM-DD-YY'))
         )
     SELECT * FROM users;
     

   • SQL Server：用CONVERT指定格式

     WITH users (user_id, action, action_date) 
     AS (VALUES 
           (1, 'start', CONVERT(DATE, '02-13-22', 101)), -- 101对应MM/DD/YY，自动兼容-分隔
           (1, 'cancel', CONVERT(DATE, '02-14-22', 101))
         )
     SELECT * FROM users;
     

   • Oracle：用TO_DATE指定格式

     WITH users (user_id, action, action_date) 
     AS (SELECT 1, 'start', TO_DATE('02-13-22', 'MM-DD-RR') FROM DUAL
         UNION ALL
         SELECT 1, 'cancel', TO_DATE('02-14-22', 'MM-DD-RR') FROM DUAL)
     SELECT * FROM users;
     

3. 验证日期运算
   转换后action_date为标准日期类型，可正常进行减法运算，比如计算天数差：

   • PostgreSQL：SELECT user_id, action_date - LAG(action_date) OVER (PARTITION BY user_id ORDER BY action_date) AS days_diff FROM users;
   • MySQL：SELECT user_id, DATEDIFF(MAX(action_date), MIN(action_date)) AS days_diff FROM users GROUP BY user_id;

补充说明 #

DBeaver的日期显示格式只是客户端的展示设置，不会改变数据库对日期字符串的解析规则。只要数据库中存储的是标准日期类型，DBeaver会自动按你设置的MM-dd-yy格式显示。

内容的提问来源于stack exchange，提问作者Giorgos Chrysanthopoulos