如何计算查询中存储为YYYYMMDD的两天之间的天数差异? [英] How can I calculate the difference in days between two days stored as YYYYMMDD in a query?

问题描述

我正在编写一个查询，该查询需要计算自日期以来以"YYYYMMDD"格式存储在数据库中的天数.由于这不是Date数据类型，因此无法使用本机Date函数.在SQL查询中执行这种计算的最佳方法是什么(在性能方面，在可读性方面等等).

I am writing a query where I need to calculate the number of days since a date that is stored in the database in the format "YYYYMMDD". Since this is not a Date datatype, I can't use native Date functions. What is the best way (performance-wise, readability-wise, etc.) to perform such a calculation in a SQL query.

推荐答案

最好?将该旧表转换为使用实际日期列.

Best? Convert that old table to use real date columns.

下一个最佳?编写数据库函数以将YYMMDD转换为实际日期.艾伦·坎平(Alan Campin)的iDate可以提供帮助.您最终会得到类似于select cvty2d(date1)-cvty2d(date2) from ...

Next best? Write a database function to convert YYMMDD to a real date. Alan Campin's iDate can help. You'd end up with something akin to select cvty2d(date1)-cvty2d(date2) from ...

总比没有好?编写丑陋的SQL，将数字转换为字符，分割字符，添加连字符，然后将THAT转换为实际日期.那只野兽看起来像

Better than nothing? Write ugly SQL to convert the number to character, split the character up, add hyphens and convert THAT to a real date. That beast would look something like

select 
  date(
     substr(char(date1),1,4) concat 
     '-' concat 
     substr (char(date1),5,2) concat 
     '-' concat 
     substr(char(date1),7,2)
  )  - 
  date(
     substr(char(date2),1,4) concat 
     '-' concat 
     substr (char(date2),5,2) concat 
     '-' concat 
     substr(char(date2),7,2)
  )   
from ...

编辑 之所以需要进行这些体操操，是因为DB2 DATE()函数希望以'YYYY-MM-DD'的形式查看字符串，并且必须使用连字符.

Edit The reason these gymnastics are necessary is that the DB2 DATE() function wants to see a string in the form of 'YYYY-MM-DD', with the hyphens being necessary.

这篇关于如何计算查询中存储为YYYYMMDD的两天之间的天数差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！