SQL语句查询Cache数据库中数据时对时间怎样转换

本文链接：https://blog.csdn.net/taxuefeilong/article/details/140081127

使用东华医为HIS系统的用户都知道，Cache数据库中对于日期的存储为几万的数字，比如，今天就是相对于1841年1月1日的第多少万天，以这种形式进行表示；对于时间，也是以数字形式存储，比如，当前时间是一天中的第多少秒，就存储多少。
这种形式存储，当想用SQL语句取值且要将日期表示成yyyy-MM-dd HH-mm-ss这种格式时，就比较困难，因为当使用SqlDb.exe取值是，能将单独的日期转换成yyyy-MM-dd 00:00:00的形式，时间能转换成HH-mm-ss，但使用“||”拼接到一起时，就不会按照yyyy-MM-dd HH-mm-ss显示，以下介绍有效的转换方法，供大家参考：
1、当日期是和时间都是数字形式时,即“67021,55291”

select 
case when b.ANA_SurgFinishTime is not null then left(DateAdd(day,b.ANA_Date,"1840-12-31"),10)||' '||RIGHT("00"||CAST(FLOOR(b.ANA_SurgFinishTime/3600) AS VARCHAR(4)),2)
||":"||RIGHT("00"||CAST(FLOOR((b.ANA_SurgFinishTime-(FLOOR(b.ANA_SurgFinishTime/3600)*3600))/60) AS VARCHAR(4)),2)
||":"||RIGHT("00"||CAST(b.ANA_SurgFinishTime-(FLOOR(b.ANA_SurgFinishTime/60)*60) AS VARCHAR(4)),2)
 else null end as 手术结束日期
 from OR_Anaesthesia b

2、东华系统在检验系统中又将日期存储成“20240624”这种形式，又该如何转换呢？

SELECT a.SurName AS 姓名,a.IDNumber AS 身份证号码,c.TextRes AS 结果,
CAST(RIGHT("0000"||CAST(FLOOR(LEFT(b.AuthDate,4)) AS VARCHAR(4)),4)
||"-"||RIGHT("00"||CAST(FLOOR((b.AuthDate/100)-(LEFT(b.AuthDate,4)*100)) AS VARCHAR(4)),2)
||"-"||RIGHT("00"||CAST(FLOOR(b.AuthDate-(LEFT(b.AuthDate,6)*100)) AS VARCHAR(4)),2)
||" "||RIGHT("00"||CAST(FLOOR(b.AuthTime/3600) AS VARCHAR(4)),2)
||":"||RIGHT("00"||CAST(FLOOR((b.AuthTime-(FLOOR(b.AuthTime/3600)*3600))/60) AS VARCHAR(4)),2)
||":"||RIGHT("00"||CAST(b.AuthTime-(FLOOR(b.AuthTime/60)*60) AS VARCHAR(4)),2) AS DATETIME) AS 审核时间 FROM dbo.RP_VisitNumber a,dbo.RP_VisitNumberReport b,dbo.RP_VisitNumberReportResult c 
WHERE b.VisitNumberDR=a.RowID AND c.VisitNumberReportDR=b.RowID AND b.AuthDate BETWEEN '20200517' AND '20200524'AND b.WorkGroupMachineDR="126"