How to add date and time

Question

Post reply

How to add date and time

polo.csit

SSCrazy

Points: 2574
More actions
September 29, 2010 at 12:25 am

#229862

Hi alll,
i am using sql server 2005,
How to add date and time
for exampleeeeee
my date file is 2010-09-29 11:53:00
and add these file 01:00:00
these 2 filed added final i want 2010-09-29 12:53:00
please help mee
regards
pols

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

declare @time datetime, @addition int

set @time = '2010-09-29 11:53:00'

set @addition = 1

select dateadd( hh, @addition,@time)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Yep, pretty much. The dateadd function can manipulate hours, minutes, seconds, days, months, years... whatever you need.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

How would you go about adding actual time fields together?

For instance, you have SQL Agent Job run times as follows:

HH:MM:SS

03:58:19

04:18:42

03:14:09

11:31:10 (which would mean 11 hours, 31 minutes, and 10 seconds)

How you would calculate this?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

You can just add them together. Example:

select

a.[Date]+a.[Time] as DatePlusTime,

a.*

from

(

select-- Test Data

[Date] = convert(datetime,'2010-09-29 11:53:00') ,

[Time] = convert(datetime,'01:00:00')

) a

Results:

DatePlusTime Date Time

----------------------- ----------------------- -----------------------

2010-09-29 12:53:00.000 2010-09-29 11:53:00.000 1900-01-01 01:00:00.000

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 5

If I wanted to calculate the average run time for a given SQL Agent job over the past month...knowing that the run_duration column in sysjobshistory is represent as 33848, meaning 3'hours, 38"Minutes, and 48Seconds...

How would I accomplish this?

run_date run_duration

2010121433848

2010121524124

2010121624745

2010121724156

2010121845317

2010122012612

20101221112524

2010122222205

2010122522724

2010122613304

2010122723235

2010122931409

2010123041842

2011010425949

2011010523143

2011010822557

2010122340551

2010122423614

2010122523204

2010122615713

2010122821853

2010123135819

2011010324137

2011010620349

2011010725759

2011010920024

2011011012631

2011011131439

2011011221940

2011011330315

2011011422543

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Serge, you should port your question on a new thread, instead of hijacking an old unrelated thread.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 7

Done, thanks for the heads up

http://www.sqlservercentral.com/Forums/Topic1048204-145-1.aspx

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience