Combine Date and Time fields in SQL Server into one column?

Подписаться 17 тыс.

Просмотров 7 тыс.

50% 1

You cannot just add Date and Time fields together in SQL Server, as you do in Excel. So how do you do it?
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: rebrand.ly/que...
98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/dat...
70-462 SQL Server Database Administration (DBA): rebrand.ly/sql...
Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql...
SQL Server Integration Services (SSIS): rebrand.ly/sql...
SQL Server Analysis Services (SSAS): rebrand.ly/sql...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/mic...
----
In Excel, you can combine Date and Time together by using the + . However, you cannot do this in SQL Server:
SELECT @Dat + @Tim
This results in an error.
In this video, we'll look at how to CAST or CONVERT the values into datetime, and then we can use the + operator to add them together.
However, what if you want to have it as a DateTime2? You cannot use the + operator then. Instead, you can use CAST (or CONVERT), DATEDIFF and DATEADD.
In this video, we'll see how you can do both of these options.
If you would like to do this as a Practice Activity, then here is the code to start with:
declare @Dat as date = '2024-01-31'
declare @Tim as time = '17:14:00'

Опубликовано:

3 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 3

@jeffmoden4332 Год назад

Nicely done and very well explained. There is a bit of a fly in the ointment though. While I agree that very few people in the world will use any dates prior to even just the year 1900, never mind prior to the year of 1753, folks need to be aware that the CombinedDateTime and the CombinedDateTime2 solutions will both fail for any DATE/TIME that's less than 1753-01-01 because of conversion errors. Again, thank you for taking the time to put this 'tube together. I always appreciate someone that will "Step up to the plate". There will also be rounding errors any time the number of milliseconds ends with a 9

@jacekk7819 Год назад

Good to know your ways Philip - here are another two easy way to combine date/time with seconds accuracy select CONCAT(@dat,@Tim) as [concat] select cast(@dat as nvarchar (20) ) +' '+ cast(@Tim as nvarchar(20)) as [cast]