I have 2 varchar fields, OrigTrxDate and OrigTrxTime. The value is 031027 and
103520. I want to get a combined datetime field with the value 2003-10-27
10:35:20.000 if they are both valid value. How can I do in SQL?

Thanks.

With all the problems aroused because we were saving year value using two
digits and still we continue doing the same.

Try:

use northwind
go

declare @d char(8)
declare @t char(6)

set @d = '20' + '031027'
set @t = '103520'

select cast(convert(char(10), cast(@d as datetime), 126) + 'T' + left(@t, 2)
+ ':' + substring(@t, 3, 2) + ':' + right(@t, 2) as datetime)



AMB

"Sean" wrote:

Sean, here's how you can do this:

SELECT
CASE WHEN ISDATE(dtstr) = 1 THEN CAST(dtstr AS DATETIME) END AS dt,
other_columns
FROM (SELECT OrigTrxDate + ' '
+ SUBSTRING(OrigTrxTime, 1, 2) + ':'
+ SUBSTRING(OrigTrxTime, 3, 2) + ':'
+ SUBSTRING(OrigTrxTime, 5, 2) AS dtstr,
other_columns
FROM YourTable) AS D

--
BG, SQL Server MVP
www.SolidQualityLearning.com


"Sean" <Sean (AT) discussions (DOT) microsoft.com> wrote

Hi

It is not clear what values you have but the following may help:

SELECT CAST(OrigTrxDate + ' ' + STUFF( STUFF( OrigTrxTime,5,0,':'), 3,0,':')
AS datetime )
FROM MyTable

John

"Sean" <Sean (AT) discussions (DOT) microsoft.com> wrote