Microsoft Access - SQL Back-end Time calculations

Hi Experts. I have a client with separate date and time fields: StartDate and StartTime, and EndDate and EndTime.

I need to calculate the difference between the two in hours and minutes (and potentially across multiple days) - and am not sure the best way to do this. Also - since converting to SQL Server - when I open the ODBC connected table from the Access side the date fields are MM/DD/YYYY - while on the SQL Server side it's a DateTime field and shows the time.

It doesn't appear that the time in the field matches the time field itself so I don't know how accurate it is since the conversion.

I was reading up on this function to combine the two fields but am not sure how to implement it -- DATEDIFF(hour,cast(StartDate +StartTime as datetime),cast(EndDate +EndTime as datetime))

I'm also wondering if I should create a stored procedure to perform this function once decided on (since I need to go back and recalcuate the history since the last developer wound up with negative results instead of positive.

Looking for some ideas, suggestions, direction. Thanks all.

Eileen
Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
How are the date and time stored? What datatype?
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
On the SQL Side they are both datetime data types...
0
Brian CroweDatabase AdministratorCommented:
Working on a possible solution...one more question what version of SQL Server are you running?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Brian CroweDatabase AdministratorCommented:
Here is a test i put together that should work

DECLARE @StartDate AS DATETIME = '8/28/2013 00:00:00.000'
DECLARE @StartTime AS DATETIME = '1/1/1900 01:02:03.000'
DECLARE @EndDate AS DATETIME = '8/29/2013 00:00:00.000'
DECLARE @EndTime AS DATETIME = '1/1/1900 13:14:15.000'

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), @StartDate, 120) + ' ' + CONVERT(VARCHAR(12), @StartTime, 114), 121) AS StartDate,
	CONVERT(DATETIME, CONVERT(VARCHAR(10), @EndDate, 120) + ' ' + CONVERT(VARCHAR(12), @EndTime, 114), 121) AS StartDate,
	DATEDIFF(HOUR, CONVERT(DATETIME, CONVERT(VARCHAR(10), @StartDate, 120) + ' ' + CONVERT(VARCHAR(12), @StartTime, 114), 121),
		CONVERT(DATETIME, CONVERT(VARCHAR(10), @EndDate, 120) + ' ' + CONVERT(VARCHAR(12), @EndTime, 114), 121)) AS HourDiff,
	DATEDIFF(MINUTE, CONVERT(DATETIME, CONVERT(VARCHAR(10), @StartDate, 120) + ' ' + CONVERT(VARCHAR(12), @StartTime, 114), 121),
		CONVERT(DATETIME, CONVERT(VARCHAR(10), @EndDate, 120) + ' ' + CONVERT(VARCHAR(12), @EndTime, 114), 121)) % 60 AS MinuteDiff

Open in new window

0
Brian CroweDatabase AdministratorCommented:
Alternatively you could use DATEADD function; however, if you want to get down into seconds or fractions of seconds in your diff then you're better off sticking with the string manipulation method as it gets a little verbose.

DECLARE @StartDate AS DATETIME = '8/28/2013 00:00:00.000'
DECLARE @StartTime AS DATETIME = '1/1/1900 01:02:03.000'
DECLARE @EndDate AS DATETIME = '8/29/2013 00:00:00.000'
DECLARE @EndTime AS DATETIME = '1/1/1900 13:14:15.000'

SELECT DATEADD(HOUR, DATEPART(HOUR, @StartTime), DATEADD(MINUTE, DATEPART(MINUTE, @StartTime), @StartDate)),
	DATEADD(HOUR, DATEPART(HOUR, @EndTime), DATEADD(MINUTE, DATEPART(MINUTE, @EndTime), @EndDate)),
	DATEDIFF(HOUR, DATEADD(HOUR, DATEPART(HOUR, @StartTime), DATEADD(MINUTE, DATEPART(MINUTE, @StartTime), @StartDate)),
		DATEADD(HOUR, DATEPART(HOUR, @EndTime), DATEADD(MINUTE, DATEPART(MINUTE, @EndTime), @EndDate))),
	DATEDIFF(MINUTE, DATEADD(HOUR, DATEPART(HOUR, @StartTime), DATEADD(MINUTE, DATEPART(MINUTE, @StartTime), @StartDate)),
		DATEADD(HOUR, DATEPART(HOUR, @EndTime), DATEADD(MINUTE, DATEPART(MINUTE, @EndTime), @EndDate))) % 60

Open in new window

0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
2009 Version 9.0000
0
lluddenCommented:
BriCrowe's answer works, but this is a bit cleaner

DECLARE @StartDate AS DATETIME = '8/28/2013 00:00:00.000'
DECLARE @StartTime AS DATETIME = '1/1/1900 01:02:03.000'
DECLARE @EndDate AS DATETIME = '8/29/2013 00:00:00.000'
DECLARE @EndTime AS DATETIME = '1/1/1900 13:14:15.000'

SELECT  datediff(MINUTE, dateadd(MINUTE,datediff(MINUTE,0,@StartTime),@StartDate), dateadd(MINUTE,datediff(MINUTE,0,@EndTime),@EndDate)) / 60 AS [HOURS],
		datediff(MINUTE, dateadd(MINUTE,datediff(MINUTE,0,@StartTime),@StartDate), dateadd(MINUTE,datediff(MINUTE,0,@EndTime),@EndDate)) % 60 AS [Minutes]

Open in new window


Basically we get the time difference in minutes from the zero date (1/1/1900) of the time piece, and add it to the date piece.  No need to mess with or worry about character conversions.
0
Scott PletcherSenior DBACommented:
I think you can just do this:
    DATEADD((<timepart>, DATEDIFF(<timepart>, 0, <time>), <date>) AS DateAndTime


For example:

DECLARE @StartDate datetime
SET @StartDate = '8/28/2013 00:00:00.000'
DECLARE @StartTime datetime
SET @StartTime = '1/1/1900 01:02:03.000'
DECLARE @EndDate datetime
SET @EndDate = '8/29/2013 00:00:00.000'
DECLARE @EndTime datetime
SET @EndTime = '1/1/1900 13:14:15.000'

SELECT
    DATEADD(SECOND, DATEDIFF(SECOND, 0, @StartTime), @StartDate) AS StartDatetime,
    DATEADD(SECOND, DATEDIFF(SECOND, 0, @EndTime), @EndDate) AS EndDatetime,
    @StartDate AS StartDate,
    @StartTime AS StartTime,
    @EndDate AS StartDate,
    @EndTime AS StartTime
0
Scott PletcherSenior DBACommented:
I suggesting adding computed columns to the table, so the values are directly available in queries for comparisons, sorting, etc.:

ALTER TABLE dbo.tablename
ADD
    StartDateTime AS DATEADD(SECOND, DATEDIFF(SECOND, 0, StartTime), StartDate),
    EndDateTime AS DATEADD(SECOND, DATEDIFF(SECOND, 0, EndTime), EndDate)


SELECT StartDateTime, ...
FROM dbo.tablename
WHERE
    StartDateTime >= ... AND
    StartDateTime < ...
ORDER BY
    StartDateTime
0
Scott PletcherSenior DBACommented:
Clarification:
"
I think you can just do this:
    DATEADD((<timepart>, DATEDIFF(<timepart>, 0, <time>), <date>) AS DateAndTime
"

<timepart> should be the lowest time part you want accuracy to:
SECOND if you want second accuracy,
MILLISECOND for millisecond accuracy,
MINUTE if you only need accuracy to the minute,
etc.
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Hey Scott - I added the computed columns but am confused at the results. I have attached screen shots of the Access query and the SQL query of the results which are odd -- not the date difference. Did I do something wrong??
TimeFields.pdf
0
Scott PletcherSenior DBACommented:
Ah, apparently there's a difference in "base" date between Access and SQL Server.  SQL uses 'Jan 1 1900' whereas Access seems to be using "Dec 30 1899".

To adjust for that, we need to change the "0" in the DATEDIFF to "-2", like so:

DATEADD(<timepart>, DATEDIFF(<timepart>, -2, <time>), <date>) AS DateAndTime
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Thanks for the response... did you notice though the date difference? The computed column shows as two days earlier??
0
Scott PletcherSenior DBACommented:
Yes; that's why I adjusted the "0" to "-2" ... but I think it should be "+2", like so:

DATEADD(<timepart>, DATEDIFF(<timepart>, +2, <time>), <date>) AS DateAndTime
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eileen MurphyIndependent Application DeveloperAuthor Commented:
-2 did the trick. Thanks a bunch!!!
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Ei0914's comment #a39468374

for the following reason:

Thanks so much as always!!!
0
Scott PletcherSenior DBACommented:
Wasn't my solution worth anything?
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
I must have done something wrong. I thought I assigned all the points to you. I will see if it lets me do it again. I'm sorry.
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
A+++++++++++++++++++++
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.