Considerations when using date fields in Voyager Prepackaged Access Reports
- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Help with using the date fields in Voyager Prepackaged Access Reports
Most of the date fields in Voyager contain both a date and a time. When you use Microsoft Access to query the Voyager tables, it normally gives you the date and time combined.
But sometimes you may want just a part of the date/time. There may be queries for which you want just the date, or just the year, or just the time, or the day of the week. There may be queries for which you want intervals of time, such as everything done in the last 30 days. There are a variety of techniques and functions (for example the "DateValue()" function, which strips out the time element) that you might use to work with date fields.
Here are some examples of date fields in Voyager that have only a date:
Here are some examples of date fields in Voyager that normally1 hold a date and time:
Most of the dates in the Voyager database actually have both the date and time recorded. The following example query shows how you might pull out each part of the date into its own column:
SELECT TOP 5
DateValue([discharge_date]) AS datevalue,
Month([discharge_date]) AS [month],
Day([discharge_date]) AS [day],
Year([discharge_date]) AS [year]
Design View and table of results from the example query shown above:
SQL example of pulling out the year portion of a date:
SELECT [Bib Text].BIB_ID, [Bib Text].TITLE, [Bib Text].AUTHOR, [Bib Text].ISBN, [Bib Text].PUB_DATES_COMBINED, [Item vw].CALL_NO, [Item vw].PERM_LOCATION, [Item vw].ITEM_ID, [Item vw].BARCODE, ITEM_STATUS_TYPE.ITEM_STATUS_DESC, ITEM_STATUS.ITEM_STATUS_DATE, Year([CREATE_DATE]) AS Create_Year
FROM (ITEM_STATUS INNER JOIN ITEM_STATUS_TYPE ON ITEM_STATUS.ITEM_STATUS = ITEM_STATUS_TYPE.ITEM_STATUS_TYPE) INNER JOIN ([Item vw] INNER JOIN ([Bib Text] INNER JOIN BIB_MFHD ON [Bib Text].BIB_ID = BIB_MFHD.BIB_ID) ON [Item vw].MFHD_ID = BIB_MFHD.MFHD_ID) ON ITEM_STATUS.ITEM_ID = [Item vw].ITEM_ID
WHERE ((([Item vw].PERM_LOCATION) Like "Main*") AND ((Year([CREATE_DATE]))=2020))
ORDER BY [Item vw].PERM_LOCATION;
Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes. Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.
1Exceptions may occur due to the data migrated from your previous system into Voyager.
See also: Using dates as criteria in Voyager Prepackaged Access Reports
- Article last edited: 18-Jan-2021