How to add time in Date Column

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)

How to add time in Date Column

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2010-07-22 : 16:49:58

I have a table
Date StartTime Endtime
2008-05-27 00:00:00.000 01:00 pm 04:00 pm

How to add time in Date Columns?

Expected OutPut:

StartDate Enddate
2008-05-27 13:00:00.000 2008-05-27 16:00:00.000

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-22 : 17:05:10

Here is one way to do it:

DECLARE @MyTable TABLE ([Date] DATETIME, StartTime VARCHAR(8), Endtime VARCHAR(8))
INSERT @MyTable
SELECT '2008-05-27 00:00:00.000', '01:00 pm', '04:00 pm'


SELECT [Date] + CAST(StartTime AS DATETIME), [Date] + CAST(EndTime AS DATETIME)
FROM @MyTable

SCHEMA
Posting Yak Master

192 Posts

Posted - 2010-07-22 : 17:07:59

Here is other way:

Declare @Var Varchar(20),@Var1 datetime,@Var2 Varchar(20)
Set @Var1 = '2010-07-22 00:00:00.000'
Set @Var = '01:00 pm'
Set @Var2 = '04:00 pm'
Select DateAdd(hh,DatePart(hour,Convert(Datetime,@Var)),@Var1) as StartDate,
DateAdd(hh,DatePart(hour,Convert(Datetime,@Var2)),@Var1) as EndDate

Thanks Lamprey

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-07-22 : 17:08:58

[code]Select
DT1 = a.DT+a.T1,
DT2 = a.DT+a.T2
from
( --Test Data
select
DT = convert(datetime,'2008-05-27 00:00:00.000'),
T1 = convert(datetime,'01:00 pm'),
T2 = convert(datetime,'04:00 pm')
) a[/code]
Results:
[code]DT1 DT2
----------------------- -----------------------
2008-05-27 13:00:00.000 2008-05-27 16:00:00.000

(1 row(s) affected)[/code]

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-23 : 04:20:45

quote:

Originally posted by Michael Valentine Jones

Select
	DT1 = a.DT+a.T1,
	DT2 = a.DT+a.T2
from
	( --Test Data
	select
		DT = convert(datetime,'2008-05-27 00:00:00.000'),
		T1 = convert(datetime,'01:00 pm'),
		T2 = convert(datetime,'04:00 pm')
	) a

Results:

DT1                     DT2
----------------------- -----------------------
2008-05-27 13:00:00.000 2008-05-27 16:00:00.000

(1 row(s) affected)

CODO ERGO SUM

Becuase DT is already of datetime datatype, you can simplify to

Select
	DT1 = a.DT+a.T1,
	DT2 = a.DT+a.T2
from
	( --Test Data
	select
		DT = convert(datetime,'2008-05-27 00:00:00.000'),
		T1 = '01:00 pm',
		T2 = '04:00 pm'
	) a

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-23 : 12:20:35

quote:
Originally posted by madhivanan

Becuase DT is already of datetime datatype, you can simplify to
Select
DT1 = a.DT+a.T1,
DT2 = a.DT+a.T2
from
( --Test Data
select
DT = convert(datetime,'2008-05-27 00:00:00.000'),
T1 = '01:00 pm',
T2 = '04:00 pm'
) a

Madhivanan

Failing to plan is Planning to fail

Now you are relying on an implicit conversion, which is not a good idea. That should probalby be:

Select
	DT1 = a.DT+CAST(a.T1 AS DATETIME),
	DT2 = a.DT+CAST(a.T2 AS DATETIME)
	...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources