Converting Date Formats in a Query
These steps show how the DATELOOKUP table can be utilized to convert date field formats for enhanced searching and sorting capabilities in the Report Builder (Query) tool.
-
Launch Tool #99 Manage My Custom Reports.
-
Select the name of an existing query in the list.
-
Click the Work With option.
-
Click the Choose Files button.
-
On the Specify File Selections screen you’ll see the file(s) that have already been included in this Query.
-
Click the Add File button (F9).
-
Enter DATELOOKUP into the File name box.
-
Click the forward arrow twice.
-
Click the Matched Records button.
-
Under the leftmost “Field Name” column, enter the name of the field with the date you want converted.
-
Under the “Comparison” column, choose EQ = Equal to.
-
Under the rightmost “Field Name” column, enter a field from the last file in the list of Available Fields that matches the formatting of the field you selected for the first file to match against.
-
For example, if your original field is in the format of MMDDYY, select the field DTMMDDYY from the DATELOOKUP file as that matches MMDDYY formatting.
-
-
Click the forward arrow.
Using the fields from DATELOOKUP, you now have multiple alternative date formats available to choose and utilize. While on the Report Builder Main Menu screen:
-
To see the alternative date formats on your report, use the Choose Fields button.
-
To apply filters with the alternative date formats, use the Choose Records button.