work days, hours time diff

[newmediaguy]

Hi Guys

I am currently upgrading a system from access to SQL server.

One aspect of the system (web app) is it takes 'quotes' then sends a mail for users to respond. The time of the quote and response (date & time) is captured but i am struggling to make the time diffence in 'working day eg 540mins 9:00am to 18:00)

Any thoughts guys?

Thanks

Glen

Glen
Conception | Execution

 

[jgoodman00]

How about the DATEDIFF function?

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[donutman]

Not sure exactly what you are looking for, but in SQL Server there is no time data type. It is datetime. So to compute duration you use the DateDiff system function as
Duration=DateDiff(mi, StartDateTime, EndDateTime)
The mi is for minutes. Look up DateDiff in BOL.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[newmediaguy]

Thanks Guys

It will get more complex when I look at the working day times(9:00am to 17:30) as everything done after 17:30 has to be wrapped round to 9:00am the next day but that can be done in the front end.

Thanks again guys



Glen
Conception | Execution

 

[donutman]

If you can be assured that both the start time end times are valid "work" times, then the computation is easy because you can use the DateDiff function to return the number of days between the two dates (just use d instead of mi for this adjustment factor) and multiply that by 930 to subtract off the "invalid" minutes between 5:30pm and 9am.
If an "invalid" time can be recorded then you have to replace the column name StartDateTime with a case statement that "changes" the time to either 9am or 5:30pm whichever is appropriate. Then the technique in the 1st paragraph should work.
But now that I think about it...it's probably better to put that sort of computation in the application. UNLESS you could do the entire "big picture" solution on the server without a row-by-row inspection of the table.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[newmediaguy]

Donutman, Thank you.

It really is a toss up between front end app or back end processing......SQL Server is so much faster at the computation than ASP Frontend will be. That said it isnt a sophsiticated process.

I really appreciate everyones comments on this.

Just to give you a better look at the 'big' picture.

The system I am building is a quotes system.

User select a category, then manufacturer, and input model numbers.

The system then find all the relevant suppliers and emails them the details, they then respond by clicking a link back to the site.

This then emails the user with a price etc and ranks the replies by fastest. This is usually in minutes but can be longer.....

Anyway i'll stop now thats an overview of what im doing

Thanks



Glen
Conception | Execution