convert datediff to d hh:mm - 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

SQL Server 2005 Forums

Transact-SQL (2005)

convert datediff to d hh:mm

Author

Topic

hanavan
Starting Member

25 Posts

Posted - 2008-03-13 : 07:18:07

I have calculated a datediff between different dates.

columns: startdate, enddate, datediff_minutes

What I want: startdate, enddate, datediff_minutes, datediff_user

I used the function:


select startdate, enddate,
convert(varchar(10), datediff(day, start, enddate)) + 'd ' + 
convert(varchar(5), dateadd(minute, datediff(minute, start, inprogresst),0),108) 
as datediff_user
from table

this works for most of the results but not for all:
2008-01-01 10:00 | 2008-01-02 11:00 | 1d 01:00 (correct)
2008-01-01 12:00 | 2008-01-02 11:00 | 1d 23:00 (false, should be 0d 23:00)

Can someone help me, I should get the user friendly results in my cube,
but I think I can calculate them before in t-sql

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-13 : 07:37:29

nope. I got 23:00 with


select convert(varchar(5), dateadd(minute, datediff(minute, '2008-01-01 12:00', '2008-01-02 11:00'), 0), 108)

KH
[spoiler]Time is always against us[/spoiler]

hanavan
Starting Member

25 Posts

Posted - 2008-03-13 : 08:10:54

if you excute the last part you get 23:00 and that's correct,
it's the first part

convert(varchar(10), datediff(day, start, enddate))

that returns 1 instead of 0.
It gives a false result (+1) when the hour of the start date is higher, then the hour of the finish date

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-13 : 08:21:19

Where do you want to show formatted data?

Madhivanan

Failing to plan is Planning to fail

hanavan
Starting Member

25 Posts

Posted - 2008-03-13 : 08:37:25

In my cube,
but it doesn't really matter where I do the conversion

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 08:54:42

This might be helpful:

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 08:55:50

Wait -- this will go in a cube? As a dimension or a value? Either way, it doesn't make any sense to convert this to a VARCHAR -- you want to either keep it as total seconds or use a DateTime.

- Jeff
http://weblogs.sqlteam.com/JeffS

hanavan
Starting Member

25 Posts

Posted - 2008-03-13 : 09:12:50

It's stored in a facttable and should show them how long it will take.

8000 minuts doesn't say them much,
they want to see 5 d(ays) 8 hours

It's a way of presentation.

I'll check the article, it should help me

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-13 : 09:33:48

I understand that it is stored the fact table, but are these values you are pivoting on, or values you are aggregating? You cannot aggregate formatted varchars -- you need to use a data type like "integer" to represent minutes. And, you are 100% correct, it is a matter of presentation, which means the solution is not formatting while inserting into the fact table, but rather by formatting and presenting the data using whichever tool you are using to view the cube. The most important thing is to use correct data types and not varchar's everywhere, and to do presentation in the proper place.

- Jeff
http://weblogs.sqlteam.com/JeffS

hanavan
Starting Member

25 Posts

Posted - 2008-03-13 : 09:43:20

thx for the help

I'll use formatting tools.
You're totally correct.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources