在最近的SQL Server版本的整数类型字段中组合日期和时间

问题描述：

我有一个包含日期列和时间列的数据库。两者都在INT类型列上。

日期

20160515

20160415

20160315

20160215

20160115

时间

190101

200101

210101

220101

230101

现在，我想将这两列合并为一个将在查询中定义的列。现在，我有查询将日期和时间转换为单独的结果。如何组合它？

I have a database that contain date column and time column..both are on INT type column.

Date
20160515
20160415
20160315
20160215
20160115
Time
190101
200101
210101
220101
230101

Now, I would like to combine these two column to be one column that will be define on queries. Right now, I have queries to convert both date and time into separate result. How to combine it?

SELECT CAST(STUFF(STUFF(STUFF(RIGHT('00000000' + 
CAST(190101 AS VARCHAR),8),3,0,':'),6,0,':'),9,0,'.') AS TIME) as sqltime

Select Convert(DATETIME, LEFT(20160515, 8)) as currdate

****这个SQL将被使用作为Visual Studio编程函数的CommandText，用于将数据发送到另一个数据库

这个SQL在附加到代码之前直接在MSSQL Management Studio查询上进行测试。

我尝试过：

SQL：INTEGER到24H格式

-------------------------

SELECT CAST（STUFF（STUFF（右（'00000000'+

CAST（190101 AS VARCHAR），8），3,0，'：'），6,0， '：'），9,0，'。'）AS TIME）as sqltime

SQL：INTEGER到24H格式包括MILLISECONDS

-----------------------------------------------

选择dateadd（小时，（190101/1000000）％100，

dateadd（分钟，（190101/10000）％100，

dateadd（第二，（190101/100）％100，

dateadd（毫秒，（190101％100）* 10，演员（'00：00：00'作为时间（2）））））） >

SQL：INTEGER TO DATE

--------------------

选择转换（DATETIME，LEFT（20130101,8））作为currdate

**** This SQL will be use as CommandText on Visual Studio programming function for sending data to another database
This SQL is testing directly on MSSQL Management Studio Queries before append to code.

What I have tried:

SQL:INTEGER TO 24H FORMAT
-------------------------
SELECT CAST(STUFF(STUFF(STUFF(RIGHT('00000000' +
CAST(190101 AS VARCHAR),8),3,0,':'),6,0,':'),9,0,'.') AS TIME) as sqltime

SQL: INTEGER TO 24H FORMAT INCLUDE MILLISECONDS
-----------------------------------------------
select dateadd(hour, (190101 / 1000000) % 100,
dateadd(minute, (190101 / 10000) % 100,
dateadd(second, (190101 / 100) % 100,
dateadd(millisecond, (190101 % 100) * 10, cast('00:00:00' as time(2))))))

SQL: INTEGER TO DATE
--------------------
Select Convert(DATETIME, LEFT(20130101, 8)) as currdate

答

实现这一目标的方法很少。例如：

There's few ways to achieve that. For example:

--uncomment below lines to return datetime
--SELECT CONVERT(DATETIME, CONCAT(NewDate, ' ', STUFF(STUFF(NewTime, 3, 0, ':'), 6, 0, ':'))) AS NewDate
--FROM (
	SELECT CONVERT(VARCHAR(10), [Date]) AS NewDate, RIGHT('000000' + CAST([Time] AS VARCHAR(6)), 6) AS NewTime
	FROM @tmp
--) AS t

退货：

Returns:

2016-05-15 09:59:59.000
2016-04-15 00:00:01.000
2016-05-15 19:01:01.000
2016-04-15 20:01:01.000
2016-03-15 21:01:01.000
2016-02-15 22:01:01.000
2016-01-15 23:01:01.000

更多信息：CAST AND CONVERT（Transact-SQL） [ ^ ]

在最近的SQL Server版本的整数类型字段中组合日期和时间

相关推荐