Combine 2 columns - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Combine 2 columns

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-12 : 10:09:55

Hi,

i have a huge db and i wanna combine "date" fields with "time" fields.

eg. date time
03/06/1979 1758
03/09/1979 1759

i wanna datetime
03/06/1979 17:58:00
03/09/1979 17:59:00

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-12 : 10:54:25

Use Proper DateTime datatype to avoid this type of conversions and related problems


declare @t table(d varchar(12), t int)
insert into @t
select '03/06/1979', 1758 union all
select '03/09/1979', 1759 
select cast(d+' '+substring(cast(t as varchar),1,2)
	+':'+substring(cast(t as varchar),3,2)+':00'  as datetime) as Dates
from @t

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-13 : 07:28:47

Here are a couple of alternatives...

--data
declare @t table(d varchar(12), t int)
insert into @t
          select '03/06/1979', 1758
union all select '03/09/1979', 1759 

--calculation
select cast(d+' '+stuff(cast(t as varchar(5)), 3, 0, ':') as datetime) as Dates from @t

--or
select dateadd(mi, t / 100 * 60 + t % 100, cast(d as datetime)) from @t

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources