I have a time column with only hours and minutes in this format: "0250" and I'd like to combine this time column with the account_date column.

The account_date column has a format like this: "2020-10-06 00:00:00.000"

I've tried something like this so far but it's not working for me:

convert(varchar(5), left(time, 2) + right(time, 2), 108)

The difference between my question and this link(combine date and time column problem) is that my "account_date" column is already in datetime format. Therefore, the existing solution doesn't work for my case

I have a time column with only hours and minutes in this format: "0250" and I'd like to combine this time column with the account_date column.

The account_date column has a format like this: "2020-10-06 00:00:00.000"

I've tried something like this so far but it's not working for me:

convert(varchar(5), left(time, 2) + right(time, 2), 108)

The difference between my question and this link(combine date and time column problem) is that my "account_date" column is already in datetime format. Therefore, the existing solution doesn't work for my case

• sql
• sql-server
• datetime

• so. in one column you have value '0250' and in other column "2020-10-06 00:00:00.000" and you want to get "2020-10-06 02:50:00.000". Right? – Sergey Commented Mar 19 at 19:51
• that's correct! – rhoops Commented Mar 19 at 19:55
• This question is similar to: combine date and time column problem. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. – siggemannen Commented Mar 19 at 20:02
• 1 ' "account_date" column is already in datetime format'; what is the data type? is it DATETIME (or its variants) or VARCHAR/CHAR? Do you want the result to have type DATETIME or VARCHAR? – tinazmu Commented Mar 19 at 21:15
• 1 my "account_date" column is already in datetime format what do you mean with datetime format ? A database has no datetime format, do you mean that it is in a varchar format that can be used to convert to a date datetype ? – GuidoG Commented Mar 20 at 8:42

2 Answers 2

The first expression gives you a datetime result, the second one is varchar. Both assume you have both columns in VARCHAR/CHAR and 24hour clock is used in your time column.

   try_cast(left(account_date,11)+ left(time, 2) + ':' + right(time, 2) as datetime) as account_date_time1,
   left(account_date,11)+ left(time, 2) + ':' + right(time, 2) + right(account_date, 7) as account_date_time2

you need to parse the values so they are iso format, and then they can be safely converted to time and datetime datatypes.
See also this guide

You have to understand that what you see as a correct date format, is depending on your system, on your regional settings and other configurations.
The only way to safely cast is to use iso types

declare @time varchar(4) = '0250'
declare @date varchar(30) = '2020-10-06 00:00:00.000'

select @time,
       @date,
       cast(replace(left(@date, 10), '-', '') + ' ' + left(@time, 2) + ':' + right(@time, 2) as datetime) 

the outcome is

the format in column3 depends on your system, your regional settings, and how the software you use to look at it shows it

See how it builds up here

declare @time varchar(4) = '0250'
declare @date varchar(30) = '2020-10-06 00:00:00.000'

select @time as time_value,
       @date as date_value,
       left(@date, 10) as date_only,
       replace(left(@date, 10), '-', '') as date_only_iso,
       left(@time, 2) + ':' + right(@time, 2) as time_iso,
       replace(left(@date, 10), '-', '') + ' ' + left(@time, 2) + ':' + right(@time, 2) datetime_iso,
       
       cast(replace(left(@date, 10), '-', '') + ' ' + left(@time, 2) + ':' + right(@time, 2) as datetime) datetime_datatype 

EDIT
As commented by Panagiotos your "format" is already in a safe format, so you can do it like this

   cast(left(@date, 10) + ' ' + left(@time, 2) + ':' + right(@time, 2) as datetime)

or more safely this

   cast(cast(@date as datetime2) as datetime)+ cast(stuff(@time, 3, 0, ':') as datetime)