date help on a data command

[toon10]

Hi

I have a Visual Basic 6 application which looks at a SQL Server 2005 back end database.

I have setup a command in the data designer which looks like this:

SELECT S1TestDate, AVG(Crossover) AS AvgOfCrossover1
FROM LDVTestRec
WHERE (Crossover > 0) AND (Crossover < 4.7)
GROUP BY S1TestDate
HAVING (S1TestDate BETWEEN CONVERT(DATETIME,
'2005-11-16 00:00:00', 102) AND CONVERT(DATETIME,
'2006-11-16 00:00:00', 102))
ORDER BY S1TestDate

This gives me the data I want but I need to replace the “having” string so that it says “BETWEEN 365 days from today’s date AND todays date”

I had this system using an Access database previously and the query looked like:

“SELECT LDVTestRec.S1TestDate, Avg(LDVTestRec.Crossover) AS AvgOfCrossover1
FROM LDVTestRec
WHERE (((LDVTestRec.Crossover)>0 And (LDVTestRec.Crossover)<4.7))
GROUP BY LDVTestRec.S1TestDate
HAVING (((LDVTestRec.S1TestDate) Between Date() And DateAdd("d",-365,Date())))
ORDER BY LDVTestRec.S1TestDate;”

Any ideas would be gratefully received.

 

[harebrain]

So close. In your Access code,

Between Date() And DateAdd("d",-365,Date())

would translate to

Between GetDate() And DateAdd("d",-365,GetDate())

 

[gmmastros]

Almost...

The code change suggested by harebrain would result in...
Between '2006-11-16 11:29AM' And '2005-11-16 11:29AM'

I just tested this on one of my tables, and it didn't return any results. You need to swap the values

Between DateAdd(Day, -365, GetDate()) And GetDate())

Since dates are stored internally as floating point numbers with the whole number part representing day, you can simply this to...

Between GetDate()-365 and GetDate()

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[toon10]

Thanks to you both for the speedy help. gmmastros, your solution worked a treat.

Andrew.