1,105,208 Community Members

Subtracting DateTime Data Type in SQL Server

Member Avatar
fayenot
Newbie Poster
23 posts since May 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I have a table for projects, it has two fields for project_start and project_end both have datetime data type. what should i do so that when the user insert dates on both field it will automatically subtract and place the answer on the duration field... please help.. oh before i forgot, the textbox for the fields are located in a formview...how can i subtract project_start and project_end and put its difference to duration field

Member Avatar
StephenP
Newbie Poster
3 posts since Jun 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

You can try using DATEDIFF, I think it's built into MS SQL. It's not terribly accurate though, in terms of defining boundaries between days, months, years, so you have to tweak around with it a bit - for each unit (seconds, minutes), you have to divide the value it returns, then put them together again.

For example:
take your:
x = DATEDIFF(@project_start, @project_end);

seconds = x / 60;
minutes = (x / 60) / 60;
And so on.

Hope I helped,
Stephen

Member Avatar
OldDude
Newbie Poster
8 posts since Mar 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 
Member Avatar
sbv
Junior Poster
178 posts since Jan 2008
Reputation Points: 5 [?]
Q&As Helped to Solve: 8 [?]
Skill Endorsements: 0 [?]
 
0
 

Try this
http://keentiger.com/2008/01/22/sql-server-datediff-duration-between-2-dates/

or Google SQL server datediff

nice link. thanks.

Member Avatar
sandeep.pande
Newbie Poster
1 post since Jul 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

select convert(varchar(10), Time2-Time1, 108)from tbl

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article