在我正在处理的提取中,我有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
在我正在处理的提取中,我有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(your_date_column AS date) + CAST(your_time_column AS datetime) FROM your_table
效果非常好
其他回答
将两个字段转换为DATETIME:
SELECT CAST(@DateField as DATETIME) + CAST(@TimeField AS DATETIME)
如果你正在使用Getdate(),首先使用这个:
DECLARE @FechaActual DATETIME = CONVERT(DATE, GETDATE());
SELECT CAST(@FechaActual as DATETIME) + CAST(@HoraInicioTurno AS DATETIME)
如果你没有使用SQL Server 2008(即你只有一个DateTime数据类型),你可以使用以下(承认粗糙和准备就绪)TSQL来实现你想要的:
DECLARE @DateOnly AS datetime
DECLARE @TimeOnly AS datetime
SET @DateOnly = '07 aug 2009 00:00:00'
SET @TimeOnly = '01 jan 1899 10:11:23'
-- Gives Date Only.
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @DateOnly))
-- Gives Time Only.
SELECT DATEADD(Day, -DATEDIFF(Day, 0, @TimeOnly), @TimeOnly)
-- Concatenates Date and Time parts.
SELECT
CAST(
DATEADD(dd, 0, DATEDIFF(dd, 0, @DateOnly)) + ' ' +
DATEADD(Day, -DATEDIFF(Day, 0, @TimeOnly), @TimeOnly)
as datetime)
虽然粗糙,但很管用!
找到这个工作的两个日期,你想从一个日期和日期从另一个:
declare @Time as datetime = '2021-11-19 12:34'
declare @Date as datetime = '2021-10-10'
SELECT @time + datediff(day, @Time, @Date)
DECLARE @Dates table ([Date] datetime);
DECLARE @Times table ([Time] datetime);
INSERT INTO @Dates VALUES('2009-03-12 00:00:00.000');
INSERT INTO @Dates VALUES('2009-03-26 00:00:00.000');
INSERT INTO @Dates VALUES('2009-03-30 00:00:00.000');
INSERT INTO @Times VALUES('1899-12-30 12:30:00.000');
INSERT INTO @Times VALUES('1899-12-30 10:00:00.000');
INSERT INTO @Times VALUES('1899-12-30 10:00:00.000');
WITH Dates (ID, [Date])
AS (
SELECT ROW_NUMBER() OVER (ORDER BY [Date]), [Date] FROM @Dates
), Times (ID, [Time])
AS (
SELECT ROW_NUMBER() OVER (ORDER BY [Time]), [Time] FROM @Times
)
SELECT Dates.[Date] + Times.[Time] FROM Dates
JOIN Times ON Times.ID = Dates.ID
打印:
2009-03-12 10:00:00.000
2009-03-26 10:00:00.000
2009-03-30 12:30:00.000
如上所述,我有很多错误,所以我这样做
try_parse(concat(convert(date,Arrival_date),' ',arrival_time) as datetime) AS ArrivalDateTime
这对我很管用。