DateDiff Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

DateDiff Problem

Author

Topic

zerohour80
Starting Member

5 Posts

Posted - 2004-06-28 : 14:06:11

Having a problem using the datediff function in access. Below is my first select statement which puts the time and date together.
SELECT BatchCards.Formulation, [Formulation] & "-" & [Batch] AS Product, [Date] & " " & [Time] AS Date_Time, BatchCards.Adjustment
FROM BatchCards;

Then my next select statement uses the info from above to calulate the datediff in hours.
SELECT visq.Product, visq.Formulation, Sum(visq.Adjustment) AS SumOfAdjustment, DateDiff('h',Min([date_time]),Max([date_time])) AS datediffh
FROM visq
GROUP BY visq.Product, visq.Formulation;

What happens is I get a negative number on some of the datediff field. I think the problem is because I am putting together the time and date field. I think it's converting it to string and that's why I can't do a date diff on that new alais field. Can anyone help?

Kristen
Test

22859 Posts

Posted - 2004-06-28 : 15:03:01

I don't think that the first parameter to DATEDIFF is intended to be a string, so replace
DateDiff('h',Min([date_time]),Max([date_time]))
with
DateDiff(h,Min([date_time]),Max([date_time]))

Someone once recommended to me that I use more "obvious" names for the first parameter, so I would suggest you use
DateDiff(Hour,Min([date_time]),Max([date_time]))
which I find more readable in the code

Kristen

zerohour80
Starting Member

5 Posts

Posted - 2004-06-28 : 15:42:37

well I tried that still the same.. Looks like the min and max is not truthly grabbing the min and max values for that product.

Kristen
Test

22859 Posts

Posted - 2004-06-28 : 16:21:04

If that's the case then perhaps! the following might do the trick:


SELECT	visq.Product, 
	visq.Formulation, 
	Sum(visq.Adjustment) AS SumOfAdjustment, 
	DateDiff(Hour, MinDate, MaxDate) AS datediffh
FROM
(
	SELECT	visq.Product, 
		visq.Formulation, 
		Sum(visq.Adjustment) AS SumOfAdjustment, 
		Min([date_time]) AS MinDate,
		Max([date_time]) AS MaxDate
	FROM	visq
	GROUP BY visq.Product, visq.Formulation;
) X

Kristen

zerohour80
Starting Member

5 Posts

Posted - 2004-06-29 : 09:06:57

Little confused on what your sql statement is trying to do..

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-01 : 13:14:37

Read the statement from the inside to the outside. That is, the inside SELECT statement is finding the Min and Max values for a Product for a Formulation. Then the outside SELECT does your datediff on the values returned by the inside SELECT.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

zerohour80
Starting Member

5 Posts

Posted - 2004-07-01 : 16:20:16

Didn't work .. Not sure if I have to play with some of the statements since I am doing this in access.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-01 : 18:16:07

Upon reviewing the Access Help, it appears that the first parameter is indeed intended to be a string, so go back and put 'h' in instead of Hour.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

Kristen
Test

22859 Posts

Posted - 2004-07-02 : 02:14:31

Oh blimey, are we in an Access thread, sorry chaps.

There's a typo in my query too


SELECT	visq.Product, 
	visq.Formulation, 
	Sum(visq.Adjustment)  AS SumOfAdjustment, 
	DateDiff(Hour or 'h', MinDate, MaxDate) AS datediffh
FROM
(
	SELECT	visq.Product, 
		visq.Formulation, 
		Sum(visq.Adjustment) AS SumOfAdjustment, 
		Min([date_time]) AS MinDate,
		Max([date_time]) AS MaxDate
	FROM	visq
	GROUP BY visq.Product, visq.Formulation;
) X

Sorry, ths is still SQL, dunno about Access

Kristen

zerohour80
Starting Member

5 Posts

Posted - 2004-07-06 : 13:44:04

Still having a problem because when I joined the date and time column in a query it turns it into a string value. So it gives the incorrect min and max. What ways are there of joing columns together? Thank you..
Below is what I am trying to do.

fields in the table
product,date,time,adjustment

info wanted
product,min(date_time),max(date_time),datediff in hours,sum adjustments
Thanks for all the help...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources