描述:
数据库表中包含完税时间、产权证时间、交易时间等三个时间,首先将完税时间与产权证时间进行对比,选出较早的一个时间,之后将改时间与交易时间进行对比,看是否在2年之内。
表数据截图:
主要思路:
将时间转化为时间戳形式,并对其时间戳之差与两年整的时间戳进行比较,得出是否为2年内相关数据。
语法:
DATEDIFF(datepart,startdate,enddate)
其中:startdate 和 enddate 参数是合法的日期表达式。
具体语法介绍详见:https://www.w3school.com.cn/sql/func_datediff.asp
部分sql解析:
(select DATEDIFF(S,'2018-01-01 00:00:00','2020-01-01 00:00:00')) as 两年整时间戳
注:从 '2020-01-01 00:00:00' 到 '2018-01-01 00:00:00' 刚好是两年整,两个时间做差并用时间戳函数 DATEDIFF 计算出两年整的时间戳,用以与计算出的时间戳数值进行比较判断;
( SELECT DATEDIFF(S, (select CONVERT(varchar(100),(select (case
when [TaxPaymentProveDateTime]<[TitleCertificateDateTime]
then [TaxPaymentProveDateTime]
else [TitleCertificateDateTime]
end
)
),23)),[TransactionDateTime]
)
) as 差值时间戳
注:
case
when [TaxPaymentProveDateTime]<[TitleCertificateDateTime]
then [TaxPaymentProveDateTime]
else [TitleCertificateDateTime]
end
通过 case when then else end 条件判断语句判断 TaxPaymentProveDateTime 与 TitleCertificateDateTime 两个时间,并选出较早时间
SELECT [Id]
,[TitleCertificateNum]
,[TaxPaymentProveDateTime]
,[TitleCertificateDateTime]
,[TransactionDateTime]
--,( SELECT DATEDIFF(S,'1970-01-01 08:00:00', (select CONVERT(varchar(100),(select (case
-- when [TaxPaymentProveDateTime]<[TitleCertificateDateTime]
-- then [TaxPaymentProveDateTime]
-- else [TitleCertificateDateTime]
-- end
-- )
-- ),23))
-- )
--) as 证明日期时间戳
--,( SELECT DATEDIFF(S,'1970-01-01 08:00:00', [TransactionDateTime])) as 交易日期时间戳
-- ,(select DATEDIFF(S,'1970-01-01 08:00:00','2020-01-01 00:00:00'))-(select DATEDIFF(S,'1970-01-01 08:00:00','2018-01-01 00:00:00')) as 两年整时间戳
,(select DATEDIFF(S,'2018-01-01 00:00:00','2020-01-01 00:00:00')) as 两年整时间戳
,( SELECT DATEDIFF(S, (select CONVERT(varchar(100),(select (case
when [TaxPaymentProveDateTime]<[TitleCertificateDateTime]
then [TaxPaymentProveDateTime]
else [TitleCertificateDateTime]
end
)
),23)),[TransactionDateTime]
)
) as 差值时间戳
,[CreateTime],[CreateUserName],[CreatorId],[ModifyDate],[ModifyUserName],[ModifyUserId],[IsShow],[Deleted]
FROM [Colder.Admin.AntdVue].[dbo].[TimeComparison]
此处sql语句中将以上查询部分看作一个新表,故被“()”包裹,并出现在关键词“from”后;
(case when 差值时间戳>两年整时间戳 then '否' else '是' end) as 是否2年内
注:此处判断差值时间戳及两年整时间戳的大小,并显示判断结果,其中“差值时间戳”以及“两年整时间戳”均为下面看作新表的表中字段名;
完整sql语句:
SELECT [Id]
,[TitleCertificateNum]
,[TaxPaymentProveDateTime]
,(select CONVERT(varchar(100),TaxPaymentProveDateTime,23)) as 完税证明时间
,[TitleCertificateDateTime]
,(select CONVERT(varchar(100),TitleCertificateDateTime,23))as 产权证时间
,[TransactionDateTime]
,(select CONVERT(varchar(100),TransactionDateTime,23))as 交易日期
--,(case when 交易日期时间戳-证明日期时间戳>两年整时间戳 then '否' else '是' end) as 是否2年内
,(case when 差值时间戳>两年整时间戳 then '否' else '是' end) as 是否2年内
,[CreateTime]
,[CreateUserName]
,[CreatorId]
,[ModifyDate]
,[ModifyUserName]
,[ModifyUserId]
,[IsShow]
,[Deleted]
from
(SELECT [Id]
,[TitleCertificateNum]
,[TaxPaymentProveDateTime]
,[TitleCertificateDateTime]
,[TransactionDateTime]
--,( SELECT DATEDIFF(S,'1970-01-01 08:00:00', (select CONVERT(varchar(100),(select (case
-- when [TaxPaymentProveDateTime]<[TitleCertificateDateTime]
-- then [TaxPaymentProveDateTime]
-- else [TitleCertificateDateTime]
-- end
-- )
-- ),23))
-- )
--) as 证明日期时间戳
--,( SELECT DATEDIFF(S,'1970-01-01 08:00:00', [TransactionDateTime])) as 交易日期时间戳
-- ,(select DATEDIFF(S,'1970-01-01 08:00:00','2020-01-01 00:00:00'))-(select DATEDIFF(S,'1970-01-01 08:00:00','2018-01-01 00:00:00')) as 两年整时间戳
,(select DATEDIFF(S,'2018-01-01 00:00:00','2020-01-01 00:00:00')) as 两年整时间戳
,( SELECT DATEDIFF(S, (select CONVERT(varchar(100),(select (case
when [TaxPaymentProveDateTime]<[TitleCertificateDateTime]
then [TaxPaymentProveDateTime]
else [TitleCertificateDateTime]
end
)
),23)),[TransactionDateTime]
)
) as 差值时间戳
,[CreateTime],[CreateUserName],[CreatorId],[ModifyDate],[ModifyUserName],[ModifyUserId],[IsShow],[Deleted]
FROM [Colder.Admin.AntdVue].[dbo].[TimeComparison] ) tb
执行结果:
代码简化:
SELECT [Id]
,[TitleCertificateNum]
,[TaxPaymentProveDateTime]
,(select CONVERT(varchar(100),TaxPaymentProveDateTime,23)) as 完税证明时间
,[TitleCertificateDateTime]
,(select CONVERT(varchar(100),TitleCertificateDateTime,23))as 产权证时间
,[TransactionDateTime]
,(select CONVERT(varchar(100),TransactionDateTime,23))as 交易日期
--,(case when 交易日期时间戳-证明日期时间戳>两年整时间戳 then '否' else '是' end) as 是否2年内
,(case when ( SELECT DATEDIFF(S, (select CONVERT(varchar(100),(select (case
when [TaxPaymentProveDateTime]<[TitleCertificateDateTime]
then [TaxPaymentProveDateTime]
else [TitleCertificateDateTime]
end
)
),23)),[TransactionDateTime]
)
)>(select DATEDIFF(S,'2018-01-01 00:00:00','2020-01-01 00:00:00')) then '否' else '是' end) as 是否2年内
,[CreateTime]
,[CreateUserName]
,[CreatorId]
,[ModifyDate]
,[ModifyUserName]
,[ModifyUserId]
,[IsShow]
,[Deleted]
FROM [TimeComparison]
注:简化代码,不通过从表获取字段,将交易日期时间戳以及两年整时间戳通过sql查询语句查询并连接在一起进行查询操作。