Subscribe To

Subscribe to SQL Server and XML by Email

Wednesday, March 25, 2009

TSQL Challenge 2 – Winners

moved to


  1. Congradulations all the winners and thank you Jacob to introduce us good challenges.

    --Ritesh Shah

  2. Not sure if this is the right place to post a question, apologies if so.

    My query is as follows:

    I have a call tracking database with two smalldatetime fields. I would like to run some reports on this database (via SQL 2005 Reporting Services) using MS SQL 2005 / Query Analyser.

    What I'm trying to extract is all the calls that haven't met a specific service level agreement (eg: all calls with a time difference of more than 4 hours between the two smalldatetime fields).

    The logic holds true from Monday to Sunday, so no need to exclude weekends, etc.

    Is there a simple query to achieve this?

    Detailed examples of what I need is shown below (Business hours 8:30 AM - 6:00 PM):


    smalldatetime1 = 16/11/2009 9:00:00
    smalldatetime2 = 17/11/2009 14:00:00

    Result (business hours) = 14 hours and 30 mins


    smalldatetime1 = 16/11/2009 9:00:00
    smalldatetime2 = 16/11/2009 12:00:00

    Result (business hours) = 3 hours

    Any help would be much appreciated.

    Thanks heaps,

  3. what you need is the DATEDIFF() function that gives the difference between two dates. Use the DATEDIFF() funciton with 'minute' flag that gives you the difference between two dates is minutes.
    See the examples given here:


About Me
Jacob Sebastian
* XQuery Tutorials
My Links
SQL Server White Papers
SQL Server 2008
My Articles
XML Workshop RSS Feed
Contact me
Free Hit Counter
Web Site Hit Counters
SQL Server Bloggers
Blog Directories
blogarama - the blog directory Programming Blogs - BlogCatalog Blog Directory
Copyright Jacob Sebastian