Results 1 to 12 of 12

Thread: Help with calculating dates.

  1. #1
    Join Date
    Aug 2005
    Posts
    13

    Help with calculating dates.

    Hi,

    I'm fairly new to MS SQL, but I'm trying to query the time elapse between two dates. Example below...

    Select
    dbo.res.book_date,
    dbo.res.arrive_date,
    Cast (dbo.translog.systemdate as datetime)
    From
    dbo.res
    Where
    dbo.res.resid > '500'

    The third line is where I need help with a statement. I need the elapse time. Ex. if the book date is 20050820100000 and the arrive date is 20050820120000, I would like the third Select statement to return 2:00 hours or something similar. Thanks.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Something like:

    datediff(hh, [book date], [arrive date])

    Can find details in sql books online.

  3. #3
    Join Date
    Aug 2005
    Posts
    13
    The query runs for a bit thenI get the error, "Syntax error converting datetime from character string. (#241)" The date is in format YYYYMMDDHHMMSS. Thanks.

    Here's the code:

    Select
    dbo.res.book_date,
    dbo.res.arrive_date,
    Datediff (hh, dbo.rers.book_date, dbo.res.arrive_date)
    From
    dbo.res
    Where
    dbo.res.resid > '500'

  4. #4
    Join Date
    Aug 2005
    Posts
    13
    OK, I think I'm getting closer...I just need help to have it show hh:mm instead of days. When I change the "d" to "hh" or "mi" i get the error, "...error converting datetime from character string."

    Codes:

    Select
    dbo.res.book_date,
    dbo.res.arrive_date,
    DateDiff (d,
    CONVERT(varchar(8), dbo.res.book_date,112),
    CONVERT(varchar(8), dbo.res.arrive_date,112)) as Difference
    From
    dbo.res
    Where
    dbo.res.resid > '500'

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    Ensure you have valid data in those date columns.

  6. #6
    Join Date
    Aug 2005
    Posts
    13
    Yes, the dates are valid.

    dbo.res.book_date = 20050815184520
    dbo.res.arrive_date = 20050816120115

    I can get it to calculate days (dd) but not hours (hh) or minutes (mi). Also, when I change the "CONVERT(varchar(8)..." to "14" I get the error "error converting datetime from character string." Thanks.

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    You may need to construct them to datetime format with substring function first.

  8. #8
    Join Date
    Dec 2004
    Posts
    502
    It's a bit involved, but here is a way to get the "2:00" that you want:

    CONVERT(char(5), DATEADD(hh, DATEDIFF(hh, STUFF(STUFF(STUFF(dbo.res.book_date, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'), STUFF(STUFF(STUFF(dbo.res.arrive_date, 9, 0, ' '), 12, 0, ':'), 15, 0, ':')), 0), 108)

    Change the "char(5)" to "char(8)" if you want to include the number of seconds between the dates.

  9. #9
    Join Date
    Aug 2005
    Posts
    13
    Quote Originally Posted by nosepicker
    It's a bit involved, but here is a way to get the "2:00" that you want:

    CONVERT(char(5), DATEADD(hh, DATEDIFF(hh, STUFF(STUFF(STUFF(dbo.res.book_date, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'), STUFF(STUFF(STUFF(dbo.res.arrive_date, 9, 0, ' '), 12, 0, ':'), 15, 0, ':')), 0), 108)

    Change the "char(5)" to "char(8)" if you want to include the number of seconds between the dates.
    Wow Nosepicker, that worked like a champ!! Thank you very much. If you don't mind, can you briefly explain what's in the statement? I'm a little confused as to what all the numbers mean. Again, thanks.

  10. #10
    Join Date
    Dec 2004
    Posts
    502
    OK, let's break down the statement like this:
    Code:
    CONVERT	(
    	char(5), 
    	DATEADD	(
    		hh, 
    		DATEDIFF	(
    				hh, 
    				STUFF(STUFF(STUFF(dbo.res.book_date, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'), 
    				STUFF(STUFF(STUFF(dbo.res.arrive_date, 9, 0, ' '), 12, 0, ':'), 15, 0, ':')
    				), 
    		0
    		), 
    	108
    	)
    Starting with the "STUFF" statements in the middle, those simply put the dates into a format that SQL Server can convert into a datetime datatype. So using the examples you provided above, '20050820100000' becomes '20050820 10:00:00' and '20050820120000' becomes '20050820 12:00:00' (because a space is inserted or "stuffed" into the 9th position, and colons are stuffed into the 12th and 15th positions). SQL Server then does an implicit conversion to datetime formats on those results. Then the "DATEDIFF" function finds the number of hours between those two dates. The result in this case is 2. Then the "DATEADD" function adds those 2 hours to "0", which is an integer representation of the date Jan. 1, 1900, 12AM ('1900-01-01 00:00:00.000'). So adding 2 hours to that date/time results in: '1900-01-01 02:00:00.000' (Jan. 1, 1900, 2AM). Then the "CONVERT" function converts the date/time '1900-01-01 02:00:00.000' into style 108, which returns just the time portion of the date/time (hh:mm:ss). So normally it would return '02:00:00'. The "char(5)" part of the CONVERT function truncates the result to the first 5 characters: '02:00'.

  11. #11
    Join Date
    Aug 2005
    Posts
    13
    Simply amazing. I really appreciate you taking the time to help myself and others on this board. I've learned so much just by reading and you do a great job of breaking down everything.

    Do you have any recommended reading materials on this subject?

  12. #12
    Join Date
    Dec 2004
    Posts
    502
    For SQL Server, I learned nearly everything by reading its help file Books Online and just by hands-on problem solving. Books Online is one of the best help files you'll ever see for an app, so it's a great place to start.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •