How to combine date from one field with time from another field - MS SQL Server

Question

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

User · Answer

SELECT CAST your date column AS date    CAST your time column AS datetime  FROM your table   Works like a charm

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

simply concatenate both   but cast them first as below select cast concat Cast DateField as varchar        Cast TimeField as varchar   as datetime  as DateWithTime from TableName

User · Answer

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

User · Answer

This worked for me  CAST Tbl date as DATETIME    CAST Tbl TimeFrom AS TIME     on SQL 2008 R2

User · Answer

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

User · Answer

SELECT CAST CAST  DateField As Date  As DateTime     CAST CAST  TimeField As Time  As DateTime

User · Answer

I ran into similar situation where I had to merge Date and Time fields to DateTime field  None of the above mentioned solution work  specially adding two fields as the data type for addition of these 2 fields is not same    I created below solution  where I added hour and then minute part to the date  This worked beautifully for me  Please check it out and do let me know if you get into any issues    with tbl as       select StatusTime    12 30 1899 5 17 00 PM   StatusDate    7 24 2019 12 00 00 AM    select DATEADD MI  DATEPART MINUTE CAST tbl StatusTime AS TIME   DATEADD HH  DATEPART HOUR CAST tbl StatusTime AS TIME    CAST tbl StatusDate as DATETIME    from tbl  Result  2019-07-24 17 17 00 000

User · Answer

You can simply add the two    if the Time part of your Date column is always zero   and the Date part of your Time column is also always zero  base date  January 1  1900    Adding them returns the correct result   SELECT Combined   MyDate   MyTime FROM MyTable   Rationale  kudos to ErikE dnolan      It works like this due to the way the date is stored as two 4-byte   Integers with the left 4-bytes being the date and the right   4-bytes being the time  Its like doing  0001 0000    0000 0001      0001 0001   Edit regarding new SQL Server 2008 types  Date and Time are types introduced in SQL Server 2008  If you insist on adding  you can use Combined   CAST MyDate AS DATETIME    CAST MyTime AS DATETIME   Edit2 regarding loss of precision in SQL Server 2008 and up  kudos to Martin Smith    Have a look at How to combine date and time to datetime2 in SQL Server  to prevent loss of precision using SQL Server 2008 and up

User · Answer

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

User · Answer

Convert both field into DATETIME     SELECT CAST  DateField as DATETIME    CAST  TimeField AS DATETIME    and if you re using Getdate   use this first   DECLARE  FechaActual DATETIME   CONVERT DATE  GETDATE     SELECT CAST  FechaActual as DATETIME    CAST  HoraInicioTurno AS DATETIME

User · Answer

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

User · Answer

Another way is to use CONCATand CAST  be aware  that you need to use DATETIME2 x  to make it work  You can set x to anything between 0-7 7 meaning no precision loss   DECLARE  date date    2018-03-12  DECLARE  time time    07 00 00 0000000  SELECT CAST CONCAT  date        time  AS DATETIME2 7     Returns 2018-03-12 07 00 00 0000000  Tested on SQL Server 14

User · Answer

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  If you used Date  amp  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

User · Answer

If the time element of your date column and the date element of your time column are both zero then Lieven s answer 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

[sql] How to combine date from one field with time from another field - MS SQL Server

Examples related to sql

Examples related to sql-server

Examples related to datetime