+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: How to combine date from one field with time from another field - MS SQL Server (/Thread-How-to-combine-date-from-one-field-with-time-from-another-field-MS-SQL-Server)
How to combine date from one field with time from another field - MS SQL Server - bardolphianczn - 07-31-2023
In an extract I am dealing with, I have 2 `datetime` columns. One column stores the dates and another the times as shown.
How can I query the table to combine these two fields into 1 column of type `datetime`?
**Dates**
2009-03-12 00:00:00.000
2009-03-26 00:00:00.000
2009-03-26 00:00:00.000
**Times**
1899-12-30 12:30:00.000
1899-12-30 10:00:00.000
1899-12-30 10:00:00.000
RE: How to combine date from one field with time from another field - MS SQL Server - Drplant3 - 07-31-2023
If you're not using SQL Server 2008 (i.e. you only have a DateTime data type), you can use the following (admittedly rough and ready) TSQL to achieve what you want:
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)
It's rough and ready, but it works!
RE: How to combine date from one field with time from another field - MS SQL Server - stethoscopist502680 - 07-31-2023
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
Prints:
2009-03-12 10:00:00.000
2009-03-26 10:00:00.000
2009-03-30 12:30:00.000
RE: How to combine date from one field with time from another field - MS SQL Server - Mrlunier293 - 07-31-2023
1. If both of your fields are datetime then simply adding those will work.
eg:
Declare @d datetime, @t datetime
set @d = '2009-03-12 00:00:00.000';
set @t = '1899-12-30 12:30:00.000';
select @d + @t
2. If you used Date & Time datatype then just cast the time to datetime
eg:
Declare @d date, @t time
set @d = '2009-03-12';
set @t = '12:30:00.000';
select @d + cast(@t as datetime)
RE: How to combine date from one field with time from another field - MS SQL Server - finegan843 - 07-31-2023
Convert the first date stored in a datetime field to a string, then convert the time stored in a datetime field to string, append the two and convert back to a datetime field all using known conversion formats.
Convert(datetime, Convert(char(10), MYDATETIMEFIELD, 103) + ' ' + Convert(char(8), MYTIMEFIELD, 108), 103)
RE: How to combine date from one field with time from another field - MS SQL Server - Mroncostman719 - 07-31-2023
This is an alternative solution without any char conversions:
DATEADD(ms, DATEDIFF(ms, '00:00:00', [Time]), CONVERT(DATETIME, [Date]))
You will only get milliseconds accuracy this way, but that would normally be OK. I have tested this in SQL Server 2008.
RE: How to combine date from one field with time from another field - MS SQL Server - rashansrvder - 07-31-2023
This worked for me
CAST(Tbl.date as DATETIME) + CAST(Tbl.TimeFrom AS TIME)
(on SQL 2008 R2)
RE: How to combine date from one field with time from another field - MS SQL Server - pyrazolone710445 - 07-31-2023
If the time element of your date column *and* the date element of your time column are both zero then [Lieven's answer][1] is what you need. If you can't guarantee that will always be the case then it becomes slightly more complicated:
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
[1]:
[To see links please register here]
RE: How to combine date from one field with time from another field - MS SQL Server - marshall108 - 07-31-2023
To combine date from a datetime column and time from another datetime column this is the best fastest solution for you:
select cast(cast(DateColumn as date) as datetime) + cast(TimeColumn as datetime) from YourTable
RE: How to combine date from one field with time from another field - MS SQL Server - gists165876 - 07-31-2023
I had many errors as stated above so I did it like this
try_parse(concat(convert(date,Arrival_date),' ',arrival_time) as datetime) AS ArrivalDateTime
It worked for me.