SQL Server: Concatenate Date and Time Column of Character DataType Into DateTime DataType

Suppose you have two columns of character datatype that stores date and time separately and you want to combine both of these columns and convert it to a valid datetime. You can use the following methods:

Consider these variables

Method 1 : Concatenate both strings and convert it into datetime

declare @date char(8), @time char(8)
select @date='20101001',@time ='12:10:47'
select cast(@date+' '+@time  as datetime)

In the above method both of them are combined to form a datetime and converted to datetime datatype

Method 2 : Convert date value into datetime and append time part to it

declare @date char(8), @time char(8)
select @date='20101001',@time ='12:10:47'
select cast(@date as datetime)+@time

In the above method, date value is converted to datetime datatype and time value is
added to it.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan