Date Diffrence in Min - 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

Date Diffrence in Min

Author

Topic

sanjay5219
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 15:12:32

Dear All,

I want diffrence between in Minutes but actually it should calculate based on working days

Ex;
2008-08-08 07:28:56.000
2008-08-12 07:28:56.000

Now 8th August is friday so answer should not calculate Saturday and Sunday

Please Help Me

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-12 : 15:18:19

select datediff(n, '2008-08-08 07:28:56.000', '2008-08-12 07:28:56.000') - 60*24*2
60*24*2 = 60 minutes * 24 hours * 2 days

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

sanjay5219
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 15:20:20

It is showing InCorrect Syntax Near 60

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-12 : 15:21:25

well you just ned to run the select. not the math explanation below.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

sanjay5219
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 15:27:39

But output is coming in date format i want in Minutes

select dateadd(d, -60*24*2, datediff(n, travel_date, travel_date)) from <tablename>
Answer is coming
1909-11-21 00:00:00.000
1896-11-26 00:00:00.000
1905-11-30 00:00:00.000
1904-05-13 00:00:00.000
1908-02-05 00:00:00.000
1896-12-14 00:00:00.000
1904-09-13 00:00:00.000
1898-10-14 00:00:00.000

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-12 : 15:30:38

try the version i edited

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

sanjay5219
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 15:33:07

I didn't get u

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-12 : 15:36:05

select datediff(n, '2008-08-08 07:28:56.000', '2008-08-12 07:28:56.000') - 60*24*2

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

sanjay5219
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 15:43:51

i ran your query
select datediff(n, '2008-08-08 07:28:56.000', '2008-08-12 07:28:56.000') - 60*24*2 : Answer got 2880
I ran another query select datediff(n, '2008-08-08 07:28:56.000', '2008-08-08 07:30:56.000') - 60*24*2 : Answer -2878

but actually for second query answer should come 2

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-12 : 15:50:39

well you'll have to make a check if there's a weekend between the 2 dates.
the best way to do this is with the use of a calendar table.
here's a nice one that will help you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources