sql - 在 SQL 中将日期 INT 转换为日期
问题描述
如何将日期整数转换为日期类型?(20200531 至 2020 年 5 月 31 日)
我当前表的数据日期格式为 YYYYMMDD(20200531、20200430 等)
根据我正在使用的 Toad Data Point 软件,datadate 的数据类型是 int。我相信它使用的是 ORACLE sql 数据库。
结果,在查询这些数据时,我必须输入 where 子句,如下所示。
where datadate = '20200531'
我的目标是将此整数数据日期转换为日期格式(2020 年 5 月 31 日),以便我可以将数据日期应用于 where 子句。
喜欢..
WHERE datadate = dateadd(DD, -1, CAST(getdate() as date))
解决方案
(如果它是一个int
专栏,请阅读下面的答案)
假设它是一个文本字符串:
假设这datadate
是一个字符串(字符、文本等)列而不是///date
列datetime
,则使用带有. 该值对应于 ISO 8601,因为这些值是按顺序排列的,即使它们缺少破折号。datetime2
datetimeoffset
CONVERT
style: 23
23
yyyy-MM-dd
此页面有style
数字参考:https ://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
SELECT
*
FROM
(
SELECT
myTable.*
CONVERT( date, datadate, 23 ) AS valueAsDate
FROM
myTable
) AS q
WHERE
q.valueAsDate = DATEADD( dd, -1, GETDATE() )
假设它是一个实际的int
列:
快速而肮脏的方法是将其转换int
为varchar
然后使用与上面相同的代码,就好像它是一个文本字段一样 - 但不要这样做,因为它很慢:
SELECT
*
FROM
(
SELECT
myTable.*,
CONVERT( char(8), datadate ) AS valueAsChar,
CONVERT( date, CONVERT( char(8), datadate ), 23 ) AS valueAsDate
FROM
myTable
) AS q
WHERE
q.valueAsDate = DATEADD( dd, -1, GETDATE() )
假设它是一个实际的int
列(更好的答案):
我们需要使用 Base-10 算法(有趣DATEFROMPARTS
)来使用和提取每个组件!
如果我们有一个表示格式化日期(恐怖)的整数,例如20200531
:
- 我们可以通过表演来获得一天
MOD 31
(例如19950707 MOD 31 == 7
) - 我们可以通过首先除以 100 来删除日期部分来获得月份,然后
MOD 12
:(例如20200531 / 100 == 202005
,202005 MOD 12 == 5
) - 我们可以通过除以
10,000
, (例如20200531 / 10000 == 2020
) 得到年份。
顺便提一句:
- SQL Server 使用
%
模运算符而不是MOD
. - 整数除法会导致截断,而不是产生十进制或浮点值(例如
5 / 2 == 2
和 not2.5
)。
像这样:
SELECT
q2.*
FROM
(
SELECT
q.*,
DATEFROMPARTS( q.[Year], q.MonthOfYear, q.DayOfMonth ) AS valueAsDate
FROM
(
SELECT
myTable.*,
( datadate % 31 ) AS DayOfMonth,
( ( datadate / 100 ) % 12 ) AS MonthOfYear,
( datadate / 10000 ) AS [Year]
FROM
myTable
) AS q
) AS q2
WHERE
q2.valueAsDate = DATEADD( dd, -1, GETDATE() )
显然,使用两个嵌套子查询是一件很痛苦的事情(SQL 的人机工程学很糟糕,我不明白 SQL 如何或为什么不允许SELECT
子句中的表达式被同一查询中的其他表达式使用 - 这真的很糟糕的人机工程学...) - 但我们可以将其转换为标量 UDF(并且SQL Server 将内联标量 UDF,因此不会影响性能)。
此函数中有一个TRY/CATCH
块,因为您可能会处理无效值,例如20209900
(这不是真实日期,因为 2020 年没有第 99 个月和第 0 天)。在这种情况下,函数返回NULL
。
CREATE FUNCTION dbo.convertHorribleIntegerDate( @value int ) RETURNS date AS
BEGIN
DECLARE @dayOfMonth int = @value % 31;
DECLARE @monthOfYear int = ( @value / 100 ) % 100;
DECLARE @year int = @value / 10000;
BEGIN TRY
RETURN DATEFROMPARTS( @dayOfMonth, @monthOfYear, @year );
END TRY;
BEGIN CATCH
RETURN NULL;
END CATCH;
END
我们可以在这样的查询中使用它:
SELECT
myTable.*,
dbo.convertHorribleIntegerDate( datadate ) AS valueAsDate
FROM
myTable
由于SELECT
无法与同一查询中的其他表达式共享表达式结果,您仍然需要使用外部查询来处理valueAsDate
(或重复dbo.convertHorribleIntegerDate
函数调用):
SELECT
*
FROM
(
SELECT
myTable.*,
dbo.convertHorribleIntegerDate( datadate ) AS valueAsDate
FROM
myTable
) AS q
WHERE
q.valueAsDate = DATEADD( dd, -1, GETDATE() )
推荐阅读
- python - 在棉花糖中选择给定标识符的对象
- drop-down-menu - Bootstrap 5 下拉导航菜单在 asp.net core mvc razor 布局页面中不起作用
- api - 当还需要一次性密码时,我使用什么 HTTP 方法来检索信息?
- just-audio - 删除锁定屏幕控制中的搜索栏
- python-3.x - 具有多重继承的 Python super().__init__()
- orocommerce - 我正在尝试在店面添加产品价格属性值(msrp,map)的新字段
- java - Java Runtime exec 发送后续命令
- nginx - Kubernetes、Ingress Nginx 和太多的重定向
- amazon-redshift - 如何在循环中多次运行红移查询
- reactjs - React 测试库 - 查询更新的 DOM