September 06, 2011

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

Concat Char Date and Time

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.

Concat Char Date and Time


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

3 Responses to "SQL Server: Concatenate Date and Time Column of Character DataType Into DateTime DataType"
  1. Anonymous said...
    September 17, 2011 at 6:43 AM

    We've only just got date and time separately - now you want to put them back together again?

  2. Sir said...
    September 17, 2011 at 6:46 AM
    This comment has been removed by the author.
  3. Sir said...
    September 17, 2011 at 6:47 AM

    We've only just got date and time separately - now you want to put them back together again?

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions