在我正在处理的提取中,我有2个datetime列。一列存储日期,另一列存储如下所示的时间。

如何查询表,将这两个字段组合成类型为datetime的1列?

日期

2009-03-12 00:00:00.000
2009-03-26 00:00:00.000
2009-03-26 00:00:00.000

1899-12-30 12:30:00.000
1899-12-30 10:00:00.000
1899-12-30 10:00:00.000

当前回答

SELECT (CAST(@TimeField As Date) As DateTime) + CAST(CAST(@TimeField As Time) As DateTime)

其他回答

如果日期列的时间元素和时间列的日期元素都为零,那么Lieven的答案就是您所需要的。如果你不能保证情况总是如此,那么事情就会变得稍微复杂一些:

SELECT DATEADD(day, 0, DATEDIFF(day, 0, your_date_column)) +
    DATEADD(day, 0 - DATEDIFF(day, 0, your_time_column), your_time_column)
FROM your_table

简单地将两者连接起来,但首先将它们强制转换,如下所示

select cast(concat(Cast(DateField as varchar), ' ', Cast(TimeField as varchar)) as datetime) as DateWithTime from TableName;

如上所述,我有很多错误,所以我这样做

try_parse(concat(convert(date,Arrival_date),' ',arrival_time) as datetime) AS ArrivalDateTime

这对我很管用。

select s.SalesID from SalesTbl s 
        where cast(cast(s.SaleDate  as date) as datetime) + cast(cast(s.SaleCreatedDate as time) as datetime) between @FromDate and @ToDate

SELECT (CAST(@TimeField As Date) As DateTime) + CAST(CAST(@TimeField As Time) As DateTime)