在 SQL 中将日期拆分为 2 列(日期 + 时间) [英] Splitting Date into 2 Columns (Date + Time) in SQL
问题描述
我正在尝试将表中的数字键转换为日期时间键.我当前的查询是:
I'm trying to convert the Date key in my table which is numeric into date time key. My current query is:
SELECT
DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
SUBSTRING([Date],10,2)+':'+
SUBSTRING([Date],12,2)+':'+
SUBSTRING([Date],14,2)+'.'+
SUBSTRING([Date],15,3))) [Date],
[Object] AS [Dataset],
SUBSTRING(Parms,1,6) AS [Media]
FROM (Select CONVERT(VARCHAR(18),[Date]) [Date],
[Object],
MsgId,
Parms
FROM JnlDataSection) A
Where MsgID = '325' AND
SUBSTRING(Parms,1,6) = 'V40449'
Order By Date DESC;
日期列显示:2013-06-22 13:36:44.403
The Date Column shows this: 2013-06-22 13:36:44.403
我想把它分成两列:日期:2013-06-22
I want to split this into two columns: Date: 2013-06-22
时间(删除微秒):13:36:44
Time (Remove Microseconds): 13:36:44
任何人都可以修改我现有的查询以显示所需的输出吗?这将不胜感激.请注意:我使用的是 SQL Server Management Studio 2008.
Can anyone modify my existing query to display the required output? That would be greatly appreciated. Please Note: I'm using SQL Server Management Studio 2008.
推荐答案
您可能想研究 convert() 函数:
You may want to investigate the convert() function:
select convert(date, getdate()) as [Date], convert(varchar(8), convert(time, getdate())) as [Time]
给予
Date Time
---------- --------
2013-07-16 15:05:43
将这些包裹在您的原始 SQL 中会导致非常丑陋:
Wrapping these around your original SQL gives the admittedly very ugly:
SELECT convert(date,
DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
SUBSTRING([Date],10,2)+':'+
SUBSTRING([Date],12,2)+':'+
SUBSTRING([Date],14,2)+'.'+
SUBSTRING([Date],15,3)))) [Date],
convert(varchar(8), convert(time,
DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
SUBSTRING([Date],10,2)+':'+
SUBSTRING([Date],12,2)+':'+
SUBSTRING([Date],14,2)+'.'+
SUBSTRING([Date],15,3))))) [Time],
[Object] AS [Dataset],
SUBSTRING(Parms,1,6) AS [Media]
FROM (Select CONVERT(VARCHAR(18),[Date]) [Date],
[Object],
MsgId,
Parms
FROM JnlDataSection) A
Where MsgID = '325' AND
SUBSTRING(Parms,1,6) = 'V40449'
Order By Date DESC;
您可能希望将其中的一部分移动到视图中,以降低复杂性.
You may want to move part of this into a view, just to reduce complexity.
这篇关于在 SQL 中将日期拆分为 2 列(日期 + 时间)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!