Need help to insert value into Datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help to insert value into Datetime

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-15 : 10:28:07

I've statement as follow,


declare @t1 table
(idx int identity(1,1), selldate char(8), selltime char(6));
insert into @t1 values('20100305','185036');
insert into @t1 values('20100318','103043');

Now, i want to insert all row in @t1 into @t2 as follow,


declare @t2 table
(idx int identity(1,1), selldate datetime);
insert into @t2
select selldate + ' ' + selltime from @t1

But return error as follow,
Msg 242, Level 16, State 3, Line 9
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Looking for help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-15 : 10:41:50

[code]
declare @t1 table
(idx int identity(1,1), selldate char(8), selltime char(6));
insert into @t1 values('20100305','185036');
insert into @t1 values('20100318','103043');

select * from @t1

--Now, i want to insert all row in @t1 into @t2 as follow,

declare @t2 table
(idx int identity(1,1), selldate datetime);
insert into @t2
select
substring(selldate,1,4)+ '-' +
substring(selldate,5,2)+ '-' +
substring(selldate,7,2)+ 'T' +
substring(selltime,1,2)+ ':' +
substring(selltime,3,2)+ ':' +
substring(selltime,5,2)
from @t1

select * from @t2
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 10:49:02

or

insert into @t2(seeldate)
select cast(selldate+' '+stuff(stuff(selltime,3,0,':'),6,0,':') as datetime) from @t1

Madhivanan

Failing to plan is Planning to fail

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-15 : 10:50:41

both answers are great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources