Examples of how to derive SQL Server dates in AVEVA Historian reports

SUMMARY

This Tech Note provides examples of how to derive different SQL Server dates such as the first day of the month, the first day of the week etc. These examples may become useful while developing Wonderware Historian reports (for instance: “average flow month to date”, “total yield this quarter” etc.).

The examples below will make use of the following Transact-SQL functions:

• GETDATE() -“ this function returns current SQL Server’s date and time
• DATEDIFF() -“ this function calculates the amount of time between two dates; it consumes the time interval as a parameter which can be expressed in hours, days, weeks, months, years etc.
• DATEADD() -“ this function calculates a date by taking an interval of time and adding it to a date, it consumes the time interval defined as provided above

Note: The last midnight can be returned using the following command:

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Upcoming midnight

SELECT CONVERT(DATETIME, CONVERT(INT, GETDATE()))

Monday of the current week

SELECT DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)

If you don’t want Sunday to be the first day of the week, you need to use a different method. This example sets Monday as the first day of the week.

SET DATEFIRST 1
SELECT DATEADD(day, 1 – DATEPART(weekday, getdate()),
DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

If you want to change the example above to calculate a different first day of the week you can adjust the SET DATEFIRST command above to the appropriate value (e.g. “SET DATEFIRST 3” for Wednesday).

Yesterday start

SELECT DATEADD(day, –1, DATEDIFF(day, 0, GETDATE()))

Section 2 – Month-Based Transact-SQL Examples

First day of the current month

SELECT DATEADD(month, DATEDIFF(month,0,GETDATE()), 0)

End of prior month

SELECT DATEADD(ms, –3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))

Last day of current month

SELECT DATEADD(ms, –3, DATEADD(mm, DATEDIFF(m, 0, GETDATE())+1, 0))

First Monday of the current month

SELECT DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, 6
– DATEPART(day, GETDATE()), GETDATE())), 0)

Section 3 – Quarter-Based Transact-SQL Examples

First day of the current quarter

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

Last day of the current quarter

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+1, –1)

Section 4 – Year-Based Transact-SQL Examples

First day of the current year

SELECT DATEADD(year, DATEDIFF(year, 0, getdate()), 0)

Last day of the current year

SELECT DATEADD(ms, –3, DATEADD(yy, DATEDIFF(yy, 0, getdate())+1, 0))

Last day of prior prior year

SELECT DATEADD(ms, –3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))

NOTE: If you need to calculate dates relative to an arbitrary date different from today, you need to change the GETDATE() function call with appropriate reference to the arbitrary date. For example, if you wanted to calculate the Last day of the year from two years ago, you could write GETDATE()-365.

Section 5 – Using These Examples in Historian Client Query

To use these examples in Historian Client Query application, set Query type to “History values” and modify the columns, criteria, retrieval, and other settings to your liking. You do not need to modify the time settings because we will change these in the SQL code. After the settings have been modified, click the SQL tab in the Results pane to see the SQL query that matches your retrieval options.

The code in the screen shot has been placed below for your convenience:

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DateAdd(mi,-5,GetDate())
SET @EndDate = GetDate()
SET NOCOUNT OFF
SELECT TagName, DateTime = convert(nvarchar, DateTime, 21), Value, vValue
FROM History
WHERE TagName IN (‘SysPerfCPUTotal’)
AND wwRetrievalMode = ‘Delta’
AND wwVersion = ‘Latest’
AND DateTime >= @StartDate
AND DateTime <= @EndDate

If we want to use the previous date examples, we can modify lines 4 and 5 according to what time we want to retrieve.

SET @StartDate = DateAdd(mi,-5,GetDate())
SET @EndDate = GetDate()

Changing the @StartDate and @EndDate variables yields the same results as changing the time settings in ActiveFactory. For example, if we want to get data ranging from last midnight until now, we would change the lines to:

SET @StartDate = CONVERT(datetime, FLOOR(CONVERT(float, GETDATE())))
SET @EndDate = GETDATE()

To get all data from last month, we would set the start date to the beginning of last month and the end date to the end of last month:

SET @StartDate = SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) – 1, 0)
SET @EndDate = SELECT DATEADD(ms, –3, DATEADD(month, DATEDIFF(month, 0,
GETDATE()), 0))