SQL GETDATE() 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

General SQL Server Forums

New to SQL Server Programming

SQL GETDATE() PROBLEM

Author

Topic

Terry2688
Starting Member

3 Posts

Posted - 2012-12-13 : 15:29:42

CAN THIS QUERY BE MADE TO RUN ANYTIME WITH ACCURATE DATA RESULTS?

/* Data is collected at 15 min. intervals */
/* Query must be ran at 11:59pm because of the getdate() command */
/* If not the values in the first and last row will not be accurate */

SELECT
CONVERT(VARCHAR, dateadd(hour, DATEDIFF (HH, GETUTCDATE(), GETDATE()),[UTCDateTime]), 101) AS 'DATE'
,CAST (MAX(ActualValue) as numeric(5,2)) AS 'MAX OAT'
,CAST (MIN(ActualValue) as numeric(5,2)) AS 'MIN OAT'
,CAST(AVG(ActualValue) as numeric(5,2)) AS 'AVG OAT'

FROM
[JCIHistorianDB].[dbo].[tblPoint],[JCIHistorianDB].[dbo].[tblActualValueFloat]

WHere
[PointName]='gadmadx1:NAE77BREF-PE01/FC-1.77_1FEC06.OA_T.Present Value' and
PointSliceId=PointId and [UTCDateTime] > getdate()-30

GROUP BY CONVERT(VARCHAR, dateadd(hour, DATEDIFF (HH, GETUTCDATE(), GETDATE()),[UTCDateTime]), 101)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-13 : 20:28:44

quote:
Originally posted by Terry2688

CAN THIS QUERY BE MADE TO RUN ANYTIME WITH ACCURATE DATA RESULTS?
<snip>

The short answer is Yes. However, I can't give you any options because I don't know what accurate means in this scenario.

What are your requirements?

If you tell us what you want we can certianly help you.

Terry2688
Starting Member

3 Posts

Posted - 2012-12-13 : 22:09:14

Data is entered every 15 minutes in the table 96 values total. If I run this query early in the day before all the data is entered the Avg,Max and Min results for my 1st and 30th row are incorrect.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-12-13 : 22:22:43

can you post some sample data to illustrate this ?

please also include the expected result

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 01:36:20

quote:
Originally posted by Terry2688

Data is entered every 15 minutes in the table 96 values total. If I run this query early in the day before all the data is entered the Avg,Max and Min results for my 1st and 30th row are incorrect.

Why not add this logic as a job? in job add the step to check max date value in table to see if it has crossed the day and if yes, add logic to find aggregates by putting filter for the elapsed day range

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Terry2688
Starting Member

3 Posts

Posted - 2012-12-14 : 10:07:12

visakh16,
Can you give an example of the logic?

Lamprey and khtan,
Here is the result from a getdate()-5 vs getdate()-7.
The query was executed earily today.
I would like for the current day not to show in the result until all data for that day has been entered.
The result for 12/09/2012 are different and I think it's because of the current time with the getdate() command.

--- DATE -- MAX - MIN - AVG
12/09/2012 81.19 65.93 73.44
12/10/2012 73.24 45.91 54.54
12/11/2012 63.80 41.75 50.30
12/12/2012 72.62 44.70 53.74
12/13/2012 76.78 45.56 57.59
12/14/2012 60.13 58.00 58.98

--- DATE -- MAX - MIN - AVG
12/07/2012 83.88 63.21 71.94
12/08/2012 86.82 68.06 73.78
12/09/2012 81.19 65.93 72.96
12/10/2012 73.24 45.91 54.54
12/11/2012 63.80 41.75 50.30
12/12/2012 72.62 44.70 53.74
12/13/2012 76.78 45.56 57.59
12/14/2012 60.13 58.00 58.98

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-12-14 : 10:28:32

add the following condition and it will exclude current day


	[UTCDateTime]	< dateadd(day, datediff(day, 0, getdate()), 0)

also change this

[UTCDateTime] > getdate()-30

[UTCDateTime] > dateadd(day, datediff(day, 0, getdate()), -30)

do note that getdate() will return the current date & time and your UTCDateTime contain datetime. Doing a getdate() - 30 will give you a date that 30 days before but at current time ! That's why your 1st record is wrong

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-14 : 12:54:26

to add to Khtan's post, if you specify the date range (excluding today) you should get what you want (also you may or may not want to use the SYSUTCDATETIME function instead of GETDATE, not sure what your requriements are):

WHERE
    [UTCDateTime] >= DATEADD(DAY, DATEDIFF(DAY, 0, SYSUTCDATETIME()), -30)
    AND [UTCDateTime] < DATEADD(DAY, DATEDIFF(DAY, 0, SYSUTCDATETIME()), 0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 14:20:31

quote:
Originally posted by Terry2688

visakh16,
Can you give an example of the logic?

Lamprey and khtan,
Here is the result from a getdate()-5 vs getdate()-7.
The query was executed earily today.
I would like for the current day not to show in the result until all data for that day has been entered.
The result for 12/09/2012 are different and I think it's because of the current time with the getdate() command.

--- DATE -- MAX - MIN - AVG
12/09/2012 81.19 65.93 73.44
12/10/2012 73.24 45.91 54.54
12/11/2012 63.80 41.75 50.30
12/12/2012 72.62 44.70 53.74
12/13/2012 76.78 45.56 57.59
12/14/2012 60.13 58.00 58.98

--- DATE -- MAX - MIN - AVG
12/07/2012 83.88 63.21 71.94
12/08/2012 86.82 68.06 73.78
12/09/2012 81.19 65.93 72.96
12/10/2012 73.24 45.91 54.54
12/11/2012 63.80 41.75 50.30
12/12/2012 72.62 44.70 53.74
12/13/2012 76.78 45.56 57.59
12/14/2012 60.13 58.00 58.98

see this to understand how to add date range logic

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources