Considerations when using date fields in Voyager Prepackaged Access Reports
- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
Question
Help with using the date fields in Voyager Prepackaged Access Reports
Answer
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:
CALENDAR.CALENDAR_BEGIN_DATE
CALENDAR.CALENDAR_END_DATE
EXCEPTION_CALENDAR.EXCEPTION_DATE
FISCAL_PERIOD.START_DATE
MAINTENANCE.DATE_IN
MAINTENANCE.DATE_OUT
MONO_CLAIM.CLAIM_DATE
MONO_CLAIM_ARCHIVE.CLAIM_DATE
MONO_SUPPLIER_REPORT.REPORT_DATE
RESERVE_LIST.EFFECT_DATE
RESERVE_LIST.EXPIRE_DATE
SERIAL_CLAIM.CLAIM_DATE
SERIAL_CLAIM_ARCHIVE.CLAIM_DATE
SERIAL_ISSUES.EXPECTED_DATE
SERIAL_SUPPLIER_REPORT.REPORT_DATE
Here are some examples of date fields in Voyager that normally1 hold a date and time:
AUTH_HISTORY.ACTION_DATE
AUTH_MASTER.CREATE_DATE
BIB_HISTORY.ACTION_DATE
BIB_ITEM.ADD_DATE
BIB_MASTER.CREATE_DATE
HEADING.CREATE_DATE
ITEM.CREATE_DATE
ITEM_STATUS.ITEM_STATUS_DATE
MFHD_HISTORY.ACTION_DATE
MFHD_MASTER.CREATE_DATE
SUBDIVISION.CREATE_DATE
Example:
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
circ_trans_archive.DISCHARGE_DATE,
DateValue([discharge_date]) AS datevalue,
Month([discharge_date]) AS [month],
Day([discharge_date]) AS [day],
Year([discharge_date]) AS [year]
FROM circ_trans_archive;
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.
Additional Information
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