How to combine date and time field in sql?

Question

I have a date field and a time field that I would like to combine in to a new datetime field.

Using MS SQL-Server

They are currently formatted like:

date: 2019-06-25 00:00:00.0000000
time: 0001-01-01 09:09:31.0000000

both fields are NVARCHAR(MAX)

I would like the end result to be:

20190625090931

How would I go about doing this?

What database are you using? #Oracle What are the fields formats? — Daniel Horvath, Jul 23 '19 at 14:17
Is it required or to be assumed that `date` will always have a zero component for the time, and `time` always has a "zero" (i.e. `0001-01-01`) component for the date? Manipulating the strings so the result can be converted is simpler than adding "arbitrary" values (i.e. `SELECT CONVERT(DATETIME2, LEFT('2019-06-25 00:00:00.0000000', 11) + RIGHT('0001-01-01 09:09:31.0000000', 16))` can then be `FORMAT`ted further, or better yet, actually stored in a column). — Jeroen Mostert, Jul 23 '19 at 14:29
Ouch...why nvarchar(max)? There are no characters that require the extended character set and none of them are going to get even close to more than 8,000 characters. Choosing the proper datatype is crucial in developing a good database. https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type — Sean Lange, Jul 23 '19 at 14:29

score 2 · Accepted Answer · edited Jul 23 '19 at 14:32

2

SELECT FORMAT(CAST(date AS date), 'yyyyMMdd') + FORMAT(CAST(time AS time), 'hhmmss')

should work...

edited Jul 23 '19 at 14:32

Sean Lange

answered Jul 23 '19 at 14:27

Michael Tobisch

2

though this will work, I have to warn about using the format function, it performs horrible. So if your table has a large number of rows this could have a serious impact on performance – GuidoG Jul 23 '19 at 14:33
1

I agree 100% with @Guido but given that the original datatypes are varchar(max) I can assume performance must not be horribly important. ;) – Sean Lange Jul 23 '19 at 14:35
1

Sorry, Sean - I just saw the comment that the code is not working, and a -1 in front of the answer, so I thought it was you... let's forget it. – Michael Tobisch Jul 23 '19 at 14:36
As the original field types are nvarchar(max) it could be done with the SUBSTRING() function as well, but this is less complicated. And I guess that performance is not so important here as Sean stated... ;-) – Michael Tobisch Jul 23 '19 at 14:38
I agree here, storing this in a `nvarchar(max)` really must mean that performance and good database design are not of any importance here – GuidoG Jul 23 '19 at 14:39
Thanks! that did what I was wanting! – Jason Hamrick Jul 23 '19 at 14:44
2

@JasonHamrick What you really should want is to fix your database design and fix the column types – GuidoG Jul 23 '19 at 14:45
it's not a production database and is only going to exist for a couple of days to update values elsewhere. – Jason Hamrick Jul 23 '19 at 17:52

1 Answers1