SQLTeam.com | Weblogs | Forums

Combine a Datetime field and a String field to make a single Datetime column

Hello I have two columns

The column on the left is a DateTime column and it represents the date of an Appointment
The column on the right (where populated) represents the time of the Appointment (e.g. row 3 is an Appointment for 11am). But is in a string field (Varchar, 5)

Can someone help me to combine the two into a single column of Datetime

Dates and Times

And what time are the blank ones? Not scheduled yet?

Yes that’s right

Does that column have any constraint on it to not allow someone to enter Mickey Mouse as string in that field :slightly_smiling_face: because it will affect the solution we might provide you and is the date column a true date time data type column?

If at all possible a redesign in the future might be a good idea, make the date column purely date data type column without time, and then make that string "time" column into an actual time data type column

1 Like

Thanks Yoiasz :slight_smile:

Yes the field only has numbers in it.
The datetime field is datetime.
Re: the re-design : I am trying to get the data out of this old weird database into a Microsoft Dynamics One so thankfully, I wont need to do any re-design :wink:

SELECT DATETIMEFROMPARTS(year(ondate),month(ondate),datepart(day,ondate),cast(left(ontime,2) as int), cast(right(ontime,2) as int), 0, 0) combined_ondate_ontime
FROM myTable
2 Likes

If you want to have the not-scheduled-yet as NULL, then below. If you just want a time of 00:00 in the combined value, remove the "NULLIF"

SELECT ondate, ondate + CAST(NULLIF(ontime, '') AS datetime) AS appointment_datetime
FROM dbo.table_name
2 Likes

Thanks a lot.
Thats really helped

The question now is... do you understand how and why it works?

Also, for future posts, my suggestion would be to post your example data as readily consumable data instead as a graphic. For example...

 SELECT  OnDate = CONVERT(DATETIME,v.Ondate)
        ,OnTime = CONVERT(VARCHAR(5),v.OnTime)
   INTO #TestTable
   FROM (VALUES
         ('20150922','')
        ,('20170728','')
        ,('20160722','11:00')
        ,('20161101','10:00')
        ,('20110125','')
        )v(OnDate,OnTime)
;
1 Like

As a bit of a sidebar, the DATETIME datatype has some incredible power built into especially when compared to the newer, seriously crippled temporal datatypes.

For example, Scott's good code explicitly converts the time string to a DATETIME before adding it to the date. Although there's certainly nothing wrong with such explicit conversions and unlike the newer temporal datatypes, DATETIME can easily withstand implicit conversions.

So this works, as well...

 SELECT CombinedDT = OnDate+ISNULL(OnTime,'')
   FROM #TestTable
;
1 Like

Will do and thanks for the tip

Hello @JeffModen

this does not work
declare @date date
set @date='2021-05-06'
select @date + 1

this works
declare @date2 datetime
set @date2='2021-05-06'
select @date2 + 1

I am reluctant to use date and using datetime everywhere in my db.
Am I doing wrong?