I have a data source that has a column for Time xx/xx/xxxx which
through DTS i can convert without any issue into the data field. There
is a second column HHmm which is military time. Whenever I try to do a
conversion to datetime i get an error even if I specify the
transformation as a datetime conversion and select the source as HHmm
and destination as hh:mm. If any one has any ideas I would appreciate
it I have been beating my head against the wall on this one.

TIA

S

This works for me

CREATE TABLE HoldsOnlyTime(TimeVal char(4))
GO
INSERT HoldsOnlyTime VALUES('2004')
INSERT HoldsOnlyTime VALUES('1918')
INSERT HoldsOnlyTime VALUES('0004')
INSERT HoldsOnlyTime VALUES('1902')


CREATE TABLE ConvertedToSmallDateTime(ColTime smalldatetime)

insert ConvertedToSmallDateTime
SELECT CAST(CONVERT(CHAR(8),left(TimeVal,2) + ':' + right(TimeVal,2),108) as
smalldatetime) from HoldsOnlyTime

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Service Tech" <naughtonservice (AT) yahoo (DOT) com> wrote