Printable View
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.
Something like:
datediff(hh, [book date], [arrive date])
Can find details in sql books online.
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'
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'
Ensure you have valid data in those date columns.
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.
You may need to construct them to datetime format with substring function first.
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.Quote:
Originally Posted by nosepicker
OK, let's break down the statement like this:
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'.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
)
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?
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.