browneye9000
asked on
Code to query time at 00:00:00.0 vs current time
Disclaimer: I am an SQL idiot. Everything I have done is copy and paste with the help of others.
I am currently using SQL to query a database located in one application called InfoPlus21 ( a process control data historian) to another application called Northwest Analytical (a statistical process control application).
This is the code I have so far.
SELECT ts, avg FROM aggregate WHERE name='T2EG:AE143201.PV' AND ts > getdbtime-500:00:00.0 AND period = 1:00
The ts stands for time stamp. The problem is that when I run the query the data is pulled starting with the getdbtime (I guess this is the current time) and then goes back for 500 1 hr intervals.
For reasons that are much too detailed to write here I need for the first time stamp to be started at the 00:00:00.0 time. I gues this may be achieved by taking the getdbtime and then going back to the first occurance of 00:00:00.0 but I'm not sure how to do this.
Please feel free to ask questions if you need more clarification but keep my disclaimer in mind.
I am currently using SQL to query a database located in one application called InfoPlus21 ( a process control data historian) to another application called Northwest Analytical (a statistical process control application).
This is the code I have so far.
SELECT ts, avg FROM aggregate WHERE name='T2EG:AE143201.PV' AND ts > getdbtime-500:00:00.0 AND period = 1:00
The ts stands for time stamp. The problem is that when I run the query the data is pulled starting with the getdbtime (I guess this is the current time) and then goes back for 500 1 hr intervals.
For reasons that are much too detailed to write here I need for the first time stamp to be started at the 00:00:00.0 time. I gues this may be achieved by taking the getdbtime and then going back to the first occurance of 00:00:00.0 but I'm not sure how to do this.
Please feel free to ask questions if you need more clarification but keep my disclaimer in mind.
ASKER
I don't think this is MS SQL server. Just a general SQL is used to connect to the db. I tried the statement in several ways and nothing worked.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT ts, [avg]
FROM aggregate
WHERE [name]='T2EG:AE143201.PV'
AND ts > CAST(CONVERT(char(8), getdbtime, 112) as datetime) -500
AND period = '1:00'