The GETDATE function produces the current date and time in SQL Server internal format for datetime values. GETDATE takes the NULL parameter ().

The following example results in the current system date and time:

SELECT GETDATE()

The DATEADD function returns a new datetime value based on adding an interval to the specified date.

Datepart is the parameter that specifies on which part of the date to return a new value (for example, year, month, day, hour, minute, and so forth), number is the value used to increment datepart, and date is the starting date.

The following example results in a date that is two days from May 29, 2005:

SELECT DATEADD([day], 2, '2005-05-29 10:10:03.001')

The DATEDIFF function returns the number of date and time boundaries crossed between two specified dates.

Datepart is the parameter that specifies on which part of the date to return a new value (for example, year, month, day, hour, minute, and so forth), startdate is the starting date, enddate is the ending date.

The following example results in the number of minutes between the first and second dates:

SELECT DATEDIFF (minute, '2005-05-29 10:10:03.001',

'2005-06-12 09:28:11.111')

The DATEPART function returns an integer representing the specified datepart of the specified date.

Datepart is the parameter that specifies on which part of the date to return, and date is the specified date.

The following example results in the month in the specified date:

SELECT DATEPART (month, '2005-05-29 10:10:03.001')

It is typical to use a combination of the Date and Time functions in Configuration Manager reports.

The following example results in the current date and time (2005-05-29 10:10:03.001 in this example) minus 100 days:

SELECT DATEADD([day], - 100, GETDATE())

The following query results in the total count of status messages for a one-day period. In this query, the COUNT, GETDATE, and DATEADD functions are used as well as the BETWEEN logical operator and the GROUP BY and ORDER BY clauses.

SELECT SiteCode, MessageID, COUNT(MessageID) AS [count],

GETDATE() AS [End Date]

FROM vStatusMessages

WHERE ([Time] BETWEEN DATEADD([day], -1, GETDATE()) AND GETDATE())

AND (MessageID BETWEEN '0' AND '10000')

GROUP BY SiteCode, MessageID

ORDER BY SiteCode, MessageID

In an inner join, records from two views are combined and added to a query's results only if the values of the joined fields meet certain specified criteria. If you use an inner join by using the ResourceID to join the v_R_System and v_GS_WORKSTATION_STATUS views, the result would be a list of all systems and their last hardware scan date.

SELECT v_R_System.Netbios_Name0 AS MachineName,

v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan]

FROM v_R_System INNER JOIN v_GS_WORKSTATION_STATUS

ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID

An outer join returns all rows from the joined views whether or not there's a matching row between them. The ON clause supplements the data rather than filtering it. The three types of outer joins (left, right, and full) indicate the main data's source. Outer joins can be particularly helpful when you have NULL values in a view.

When you use a left outer join to combine two views, all the rows in the left view are included in the results. In the following query, the v_R_System and v_GS_WORKSTATION_STATUS views are joined using the left outer join. The v_R_System view is the first view listed in the query, making it the left view. The result will include a list of all systems and their last hardware scan date. Unlike the inner join, systems that have not been scanned for hardware will still be listed with a NULL value (as seen in the result set).

SELECT v_R_System.Netbios_Name0 AS MachineName,

v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan]

FROM v_R_System LEFT OUTER JOIN v_GS_WORKSTATION_STATUS

ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID

A right outer join is conceptually the same as a left outer join except that all the rows from the right view are included in the results.

A full outer join retrieves all the rows from both joined views. It returns all the paired rows where the join condition is true, plus the unpaired rows from each view concatenated with NULL rows from the other view. You usually won't want to use this type of outer join.

A cross join returns the product of two views, not the sum. Each row in the left view is matched up with each row in the right view. It's the set of all possible row combinations, without any filtering. However, if you add a WHERE clause, a cross join functions as an inner join—it uses the condition to filter all possible row combinations down to the ones you want.

A self join uses any of the above join types, but is a view that is joined to itself. In database diagrams, a self join is called a reflexive relationship.