Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 27th, 2005, 04:31 PM
Registered User
 
Join Date: Jun 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmwiggins
Default DateDiff in SQL using Crystal Reports 10

Please help!! I am trying to get this formula to convert to dd:hh:mm

datediff("h", {Problem_Management.Date Opened},{Problem_Management.Date n Time Resolved by})

Right now it only converts to hours I need Days:hours:minutes

All suggestions are welcome.

 
Old June 27th, 2005, 09:05 PM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

DateDiff returns the difference between two dates. I cannot find a way to return Days:hours:minutes.

 
Old August 10th, 2006, 10:23 AM
Registered User
 
Join Date: Aug 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi
Had a similar issue!
Heres how I cracked it...
Number of Days
DATEDIFF(hh, FirstDate, SecondDate)/ 24 AS NoDays,
Number of Hours
0 - (DATEDIFF(hh, FirstDate, SecondDate) / 24 * 24 - DATEDIFF(hh, FirstDate, SecondDate)) AS NoHrs,
Number of Minutes
0 - (DATEDIFF(minute, FirstDate, SecondDate) / 60 * 60 - DATEDIFF(minute, FirstDate, SecondDate)) AS NoMins

Hope this helps ;)
 
Old August 10th, 2006, 11:21 AM
Registered User
 
Join Date: Aug 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Further to this I have included the code required to build a view using the Day,Hr,Min formula. And for smartness puts in the Colons.

SELECT FirstDate, SecondDate, CONVERT(Varchar, DATEDIFF(hh, FirstDate, SecondDate) / 24) + ':' + CONVERT(Varchar, 0 - (DATEDIFF(hh, FirstDate, SecondDate) / 24 * 24 - DATEDIFF(hh, FirstDate,
           SecondDate))) + ':' + CONVERT(Varchar, 0 - (DATEDIFF(minute, FirstDate, SecondDate) / 60 * 60 - DATEDIFF(minute, FirstDate, SecondDate))) AS NoDaysHrsMins
FROM Database.Table

As you can guess just change the tablename to your table & replace the FirstDate,SecondDate with your date fields.

Messy but I can see no other way of doing this.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Crystal Reports 10 With .Net 3.5 subbukbc BOOK: Professional Crystal Reports for VS.NET 2 June 24th, 2011 05:08 AM
Crystal Reports 10 without CrystalActiveXReport !! ahmed_hammam70 Pro VB 6 4 May 21st, 2007 02:31 AM
Vb6 with crystal reports 10 Eleakim Cribe Pro VB 6 0 June 22nd, 2006 09:20 AM
Crystal reports 10 XML handling ferozkhan XML 1 August 25th, 2004 08:33 AM
Crystal reports 10 with parameters HELP! stevecobbett Crystal Reports 0 March 18th, 2004 09:58 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.