Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    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:

    clipboard_e3df043d8e98c1f4214dc230ab3f63bad.png

    SQL example of pulling out the year portion of a date:

    clipboard_ece0380fdc847abbfd2bc78b92683c1b2.png

     

    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