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

1

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

2

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

3

Yes that’s right

4

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
5

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:

6 
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
7

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
8

Thanks a lot.
Thats really helped

9

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
10

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
11

Will do and thanks for the tip

12

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?

13

The only time I use DATE is when it's absolutely guaranteed that no time element will ever be used in the table. The use of DATE does save 5 bytes and, with no time element, there's usually no requirement for temporal calculations that are better suited by DATETIME.

I find that's incredibly rare and so, like you, I just use DATETIME in most places.

14

p.s.
I also find that people that have both a DATE and a TIME column in a table suffer greatly when it comes to temporal calculations. It's just not worth it compared to having a single DATETIME column.

15

Don't use datetime unless you actually need a time. That is, for date values, use a date -- pretty basic design rule.

If you need to add a day to a date, do it the standard way:

DATEADD(DAY, 1, date_column_or_variable)

16

That's kind of what I was alluding to... and that it's also fairly rare to not actually need a time. :wink:

17

Thanks to all for guidance.