Retrieve elements of element hierarchy as it exists at a certain date

SELECT Name FROM NuGreen.Asset.vElementHierarchy
WHERE Path = N'\NuGreen\Houston\Cracking Process\Equipment\'
/* "Path = …" condition restricts hierarchy traversal to
single
- element*/
AND Effective <= N'01-Aug-2011' /*query date*/
AND Ineffective > N'01-Aug-2011 /*query date*/

For the query date specification, you can use one of the following time stamp literal formats:

• * (refers to the current time)

• yyyy-mm-dd hh:mm:ss.fr

• dd-mmm-yyyy hh:mm:ss.fr

In most cases, it is desirable to specify query date as shown above. Without query date restriction, query into vElementHierarchy traverses entire element hierarchy history.