I have a table (once again implemented by the bio-metric software) that stores date and time as strings in different columns.
Date column stores date like 20160128 for January 18 2016 and the time column stores the time like 220747 (10:07:47 PM).
How I can concatenate both the strings and convert to a valid datetime value?
I have referred few of the other questions asked here already and found many of them only refers to the date part, missing time factor
You should check out the help pages of Convert and see what best fits your needs: https://msdn.microsoft.com/en-us/library/ms187928.aspx
SELECT Convert(datetime, '20160128' + ' ' + '22:07:47', 113)
seems to work, so I'd suggest you simply concatenate the date as is and enrich the time part as needed (using SubString(), take care to add a starting zero when the hour part has only 1 number)
The following is an example script that converts from a date and time stored in VARCHAR columns. If formatted as YYYYMMDD[ hh:mm:ss[.mmm]] (ISO 8601 format) this can be cast to a DATETIME:
DECLARE @dtpart VARCHAR(16) = '20160128';
DECLARE @timepart VARCHAR(16) = '220747';
SELECT
CAST(@dtpart+' '+LEFT(@timepart,LEN(@timepart)-4)+':'+SUBSTRING(@timepart,LEN(@timepart)-3,2)+':'+RIGHT(@timepart,2) AS DATETIME)
If your date/time strings are stored in CHAR columns it's a little more involved as strings are padded with spaces. This requires trimming the spaces using RTRIM:
DECLARE @dtpart CHAR(16) = '20160128';
DECLARE @timepart CHAR(16) = '20747';
SELECT CAST(RTRIM(@dtpart) + ' ' + LEFT(RTRIM(@timepart),LEN(RTRIM(@timepart))-4)+ ':' + SUBSTRING(RTRIM(@timepart),LEN(RTRIM(@timepart))-3,2) + ':'+RIGHT(RTRIM(@timepart),2) AS DATETIME)
If you can manipulate the strings into a format SQL server can natively parse, you can then just cast it - e.g. yyyyMMdd hh:mm:ss format:
declare @date char(8)
set @date = '20160128'
declare @time char(6)
set @time = '220747'
select cast(@date + ' ' + substring(@time, 1, 2) + ':' + substring(@time, 3, 2) + ':' + substring(@time, 5, 2) as datetime)
Try this..
declare @val varchar(10) = '20160128'
declare @va2 varchar(10) = '220747'
select convert(datetime,cast(convert(date, @val, 121) as varchar(10)) +' '+substring(cast(@va2 as varchar (6)), 1, 2)+':'+substring(cast(@va2 as varchar(6)), 3, 2)+':'+substring(cast(@va2 as varchar(6)), 5, 2)) as Date
Try this:-
DECLARE @dtpart VARCHAR(16) = '20160128';
DECLARE @timepart VARCHAR(16) = '220747';
DECLARE @t DateTIME
SET @t = LEFT(@timepart,LEN(@timepart)-4)+':'+SUBSTRING(@timepart,LEN(@timepart)-3,2)+':'+RIGHT(@timepart,2)
select LEFT(convert(varchar(50),Convert(datetime, @dtpart ,100)),11)+' '+ LTRIM(RIGHT(CONVERT(CHAR(20), @t, 22), 11))
CHAR(XXX), orVARCHAR(XXX)?