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, 2019 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 FORMATted further, or better yet, actually stored in a column). — Jeroen Mostert, Jul 23, 2019 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. sqlblog.org/2009/10/12/… — Sean Lange, Jul 23, 2019 at 14:29

Sean Lange · Accepted Answer · 2019-07-23 14:32:00Z

2

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

should work...

Sean Lange

33.4k3 gold badges26 silver badges40 bronze badges

answered Jul 23, 2019 at 14:27

Michael Tobisch

1,0466 silver badges16 bronze badges

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, 2019 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, 2019 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, 2019 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, 2019 at 14:38
2

@JasonHamrick What you really should want is to fix your database design and fix the column types
– GuidoG
Jul 23, 2019 at 14:45

| Show 3 more comments

1 Answer 1