Attach date and time SQL

srk 41 Reputation points
2022-06-23T08:58:27.093+00:00

Hi there,
Can you please help me on this ?
I want to take date from first column and time from second column, need to put them in the new column.

2015-10-05 09:30:00.000
2015-10-07 16:00:00.000

create table #temp ( dateIntrvw datetime, InterViewTime datetime)   
  
insert into #temp   
select '2015-10-05 00:00:00.000','2015-10-04 09:30:00.000'  
union all   
select '2015-10-07 00:00:00.000','2015-10-05 16:00:00.000'  

Many thanks in advance

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,262 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Bert Zhou-msft 3,411 Reputation points
    2022-06-23T09:08:39.617+00:00

    Hi,@srk

    Try this:

    select cast(dateadd(DAY, 0, datediff(DAY, 0, dateIntrvw)) AS datetime)  
     + cast(dateadd(DAY, 0 - datediff(DAY, 0, InterViewTime),InterViewTime) AS datetime)  
        from #temp  
    

    214276-image.png
    Bert Zhou

    1 person found this answer helpful.
    0 comments No comments

  2. Jingyang Li 5,891 Reputation points
    2022-06-23T19:00:40.473+00:00
      SELECT dateIntrvw, InterViewTime,   
      Cast( Format(dateIntrvw ,'yyyy-MM-dd') +' '+ Format(InterViewTime ,'HH:mm:ss.sss') as datetime)    
      FROM #temp;  
    
    1 person found this answer helpful.
    0 comments No comments

  3. Tom Phillips 17,711 Reputation points
    2022-06-23T12:35:34.247+00:00

    I find it more straight forward to use string formats.

     SELECT   
    	TRY_CONVERT(datetime,CONCAT(CONVERT(varchar(25),dateIntrvw, 23),' ',CONVERT(varchar(25),InterViewTime, 114)), 121)  
     FROM #temp  
    
    0 comments No comments

  4. Naomi 7,361 Reputation points
    2022-06-23T13:20:39.573+00:00

    Try this neat and simple solution:

    create table #temp ( dateIntrvw datetime, InterViewTime datetime)   
          
     insert into #temp   
     select '2015-10-05 00:00:00.000','2015-10-04 09:30:00.000'  
     union all   
     select '2015-10-07 00:00:00.000','2015-10-05 16:00:00.000'  
      
     SELECT dateIntrvw, InterViewTime, dateIntrvw + CAST(CAST(InterViewTime AS TIME) AS DATETIME) AS InterviewDateTime FROM #temp;  
    
    0 comments No comments

  5. Jingyang Li 5,891 Reputation points
    2022-06-23T14:09:29.433+00:00
     --DATETIMEFROMPARTS ( year, month, day, hour, minute, second, millisecond )  
     Select dateIntrvw,InterViewTime  
     , datetimefromparts(Year(dateIntrvw),month(dateIntrvw),day(dateIntrvw)  
     ,datepart(hour,InterViewTime),datepart(minute,InterViewTime),datepart(second,InterViewTime),datepart(millisecond,InterViewTime)  
     ) NewInterViewDateTime   
     from #temp  
      
    
    0 comments No comments