Actions that include date and time values, such as “get current date and time”, “50 days from today is what date?”, or “find out what day of the week falls on a certain date” are common. When you write queries by using the SMS Query Builder interface, or when you compose reports from information stored in the SMS site database, you can express the date and time in any valid SQL format. An example would be any expression that has a SQL Server datetime data type or that can be converted implicitly, such as an appropriately formatted character string — “1998.10.31” for example.
Storage of times in the SMS site database could be local or Greenwich mean time (GMT). The status message viewer can convert to local time, but queries and reports cannot. What you see might be seven hours later than expected, if local time is Pacific Daylight. Therefore, the user must be aware of the following:
Crystal Reports will display what is stored and cannot make time zone conversions. If the report concerns status messages, label the times GMT. If it is an inventory report, label the times as local time. For advertisement reports, you have to check the value of AssignedScheduleIsGMT to get the time style being used. This property is “lazy,” meaning it is not exposed to the Query Builder or to Crystal Reports, but you can view it using WBEMTEST. See Chapter 8, “Using Network Discovery Architecture,” in the section, “Accessing Discovery Data.”
Depending on the context, you may encounter time notations in the following universal format:
19981118175900000000+***
Key:
| 1998 | year |
| 11 | month |
| 18 | day |
| 1759 | hour |
| 00 | second |
| 000000 | microsecond |
| +*** | offset from local time |
The following table lists valid datetime formats that you can use with the Query Builder:
Table B.1 Valid Datetime Formats
| Style number without century | Style number with century | Type | Output style |
|---|---|---|---|
| – | 0 or 100 | Default | mon dd yyyy hh:mm |
| 1 | 101 | USA | mm/dd/yyyy |
| 1 | 102 | ANSI | yyyy.mm.dd |
| 3 | 103 | British/French | dd/mm/yyyy |
| 4 | 104 | German | dd.mm.yyyy |
| 5 | 105 | Italian | dd-mm-yyyy |
| 6 | 106 | – | dd-mon-yyyy |
| 7 | 107 | – | mon.dd.yyy |
| – | 8 or 108 | – | hh:mm:ss |
| – | 9 or 109 | – | mon dd yyyy
hh:mi:ss:mmmAM (or PM) |
| 10 | 110 | USA | mm-dd-yy |
| 11 | 111 | JAPAN | yy/mm/dd |
| 12 | 112 | ISO | yymmdd |
| – | 13 or 113 | – | dd mon yyyy
hh:mi:ss:mmm (24 h) |
| 14 | 114 | – | hh:mi:ss:mmm (24 h) |
Besides full datetime formats, you can also use datepart formats, which are also valid for the Query Builder or for writing reports from the SMS site database. Datepart formats provide only part of the full datetime format, the year, or just the day of the month, for example. The following table lists valid datepart formats:
Table B.2 Valid Datepart Formats
| datepart Value | Abbreviation | Limits |
|---|---|---|
| Year | Yy | 1753-9999 |
| Month | Mm | 1-12 |
| Day | Dd | 1-31 |
| Hour | Hh | 1-23 |
| Minute | Mi | 0-59 |
| Second | Ss | 0-59 |
| Millisecond | Ms | 0-999 |