在T-SQL中将DateTime分解为两列

I have a datetime field (e.g. 2013-07-02 01:14:32.000) that I'm trying to split into two columns with the Alias Column name of "Date Added" and "Time Added:". I would only like to see the results of all the data that consists in the 4 day span prior to the current date. I would also like to cut out the microseconds. Is this an easy fix or not? I'm still learning the ropes of SQL Server Management Studio 2008. Can anyone edit my query to display the correct output?

我有一个datetime字段(例如:2013-07-02 01:14:32.000)，我试图将其拆分为两列，列名为“Date Added”和“Time Added:”。我只希望看到在当前日期之前的4天内包含的所有数据的结果。我还想去掉微秒。这是一个简单的解决方法吗?我还在学习SQL Server Management Studio 2008的技巧。谁能编辑我的查询以显示正确的输出?

Existing Query:

  Select as_key AS [Key:], as_name AS [Server Name:], as_introdate AS [Date Added:]
  from AutomationStations
  Order By [Date Added:] desc

Sample Output from Existing Query:

  Key: 0001
  Server Name: Server1
  Date Added: 2013-07-02 01:14:32.000

3 个解决方案

#1

Easiest way it to extract the desired parts from built-in date formats:

从内置的日期格式中提取所需部件的最简单方法:

SELECT 
    as_key AS [Key:], 
    as_name AS [Server Name:],
    CONVERT(VARCHAR(10),as_introdate,101) AS [Date Added:],
    CONVERT(VARCHAR(8) ,as_introdate,108) AS [Time Added:]
FROM AutomationStations
WHERE DATEDIFF(d,as_introdate,GETDATE()) <= 4
ORDER BY as_introdate DESC

#2

Or an alternate method casting to Date and Time datatypes..

或另一种方法强制转换到日期和时间数据类型。

SELECT 
    as_key AS 'Key:', 
    as_name AS 'Server Name:',
    CAST(as_introdate AS DATE) 'Date Added:',
    CAST(as_introdate AS TIME(0)) AS 'Time Added:' 
FROM AutomationStations
WHERE DATEDIFF(DAY,as_introdate,GETDATE()) <= 4
ORDER BY as_introdate DESC

#3

-1

CONVERT(DATE, YOUR_SELECTED_DATETIME) AS DATE,
CONVERT(TIME, YOUR_SELECTED_DATETIME) AS TIME

This way you keep your data types.

这样就可以保存数据类型。

#1