Search
Select by making comparisons against the desired extraction from the datetime field specified in the Use parameter, using the given Condition, compared to the given value or values, which can be values from a field, a specified value, or the result of an expression that evaluates to a numeric value or to a datetime value depending on the data type of the extraction specified in the Use parameter.
Use options:
- date and time - Use the full datetime values in the source field.
- date without time - Use the datetime values in the source field but setting all time portions of the data time value to zero. For example, the date without time result for 12/25/2016 13:43:53 would be 12/25/2016 0:00:00.
- day - Use the day of the month number of the datetime values in the source field. For example, the day of the month number for a datetime value of 12/25/2016 13:43:53 would be 25.
- hour - Use the hour of the day number, using 24 hour format, of the datetime values in the source field. For example, the hour of the day number for 12/25/2016 13:43:53 would be 13.
- millisecond - Use the millisecond number of the datetime values in the source field. Many data sets do not bother recording greater time detail than seconds, so the millisecond number of datetime values is often zero.
- minute - Use the minute of the hour number of the datetime values in the source field. For example, the minute of the hour number for 12/25/2016 13:43:53 would be 43.
- month - Use the month of the year number of the datetime values in the source field. For example, the month of the year number for 12/25/2016 13:43:53 would be 12.
- second - Use the second of the minute number of the datetime values in the source field. For example, the second of the minute number for 12/25/2016 13:43:53 would be 53.
- week - Use the computed week of the year number of the datetime values in the source field. Choosing the week option will open a Week start parameter box allowing specification of the day of the week (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, or Saturday) on which a week is considered to begin. Choosing a different Week start day will often change the computed week of the year. For example, 12/25/2016 13:43:53 falls on a Sunday. The week of the year number for 12/25/2016 13:43:53 using a Week start day of Sunday would be 53. Using a Week start day of Monday for that same datetime value would result in a week of the year number of 52.
- week day - Use the computed day of the week number, from 1 to 7, of the datetime values in the source field. Choosing the week day option will open a Week start parameter box allowing specification of the day of the week (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, or Saturday) on which a week is considered to begin. Choosing a different Week start day will often change the computed day of the week number. For example, 12/25/2016 13:43:53 falls on a Sunday. The day of the week number for 12/25/2016 13:43:53 using a Week start day of Sunday would be 1. Using a Week start day of Monday for that same datetime value would result in a day of the week number of 7.
- year - Use the year number of the datetime values in the source field. For example, the year number for 12/25/2016 13:43:53 would be 2016.
- year day - Use the computed day of the year number, from 1 to 365, of the datetime values in the source field. For example, the day of the year number for 12/25/2016 13:43:53 would be 360.
Condition options:
- between - Select records with values between the specified At least and At most values, inclusive.
- equal (=) - Select records with values equal to the specified value.
- greater (>) - Select records with values greater than the specified value.
- greater or equal (>=) - Select records with values greater than or equal to the specified value.
- less (<) - Select records with values less than the specified value.
- less or equal (<=) - Select records with values less than or equal to the specified value.
- not equal (<>) - Select records with values not equal to the specified value.
If we would like to select records that are logical NOT variations of the above, for example not between, select on the basis of one of the above and press Ctrl-I or Edit - Select Inverse to invert the selection. Selecting using between and then pressing Ctrl-I is the equivalent of "not between" the given values.
Launch the template by choosing a datetime field in the Select pane and then double-clicking the Search template. When the template launches we can specify options.
Search : date and time
Search based on full datetime values in the source field.
We start with a table with a single Date field of type datetime. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Date field and then we double-click the Search template to launch it.
In the Search template we choose date and time as the Use option. For the Condition option we choose less or equal (<=).
For the Value we enter 16-Dec-2016. Entering just the date part of a datetime literal is the same as entering the date time with a time part of 0:00:00. If we wanted to enter a full datetime literal, including the time part, we could have entered a value such as 16-Dec-2016 12:15:02, for two seconds after 12:15 (fifteen minutes after noon) on that date.
For the Action, we use the default replace selection option.
Press Select.
The template selects all records in the Date field where the datetime value is less than 12/16/20 at a time of 0:00:00. Note that the fifth record in the table is not selected, since the Date value for that record, 12/16/2016 4:24:34, comes after the literal datetime we entered, 12/16/2016 0:00:00.
Search : date without time
Search based on datetime values in the source field, but setting all time portions of those data time values to zero. For example, the date without time result for 12/25/2016 13:43:53 would be 12/25/2016 0:00:00.
This transform option is useful when making comparisons to date literals where we do not care about the time part of the date. It is more convenient in such cases to simply enter the date instead of the additional keyboarding required to enter both a date and a time.
We start with a table with a single Date field of type datetime. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Date field and then we double-click the Search template to launch it.
In the Search template we choose date without time as the Use option. For the Condition option we choose equal (=).
For the Value we enter 16-Dec-2016. Entering just the date part of a datetime literal is the same as entering the date time with a time part of 0:00:00. Since the values which we compare in the Date field are truncated by the date without time option to only the date part, we do not need to enter a time part in the Value box.
For the Action, we use the default replace selection option.
Press Select.
The template selects all records in the Date field where the date portion is 12/16/2016, not considering the time portion.
If we had used the date and time option in the Use box, we would have had to enter the full date and time as a literal value for the equal (=) condition to be useful.
Search : day
Search based on the day of the month number of the datetime values in the source field. For example, the day of the month number for a datetime value of 12/25/2016 13:43:53 would be 25.
Search : hour
Search based on the hour of the day number, using 24 hour format, of the datetime values in the source field. For example, the hour of the day number for 12/25/2016 13:43:53 would be 13.
Search : millisecond
Search based on the millisecond number of the datetime values in the source field. Many data sets do not bother recording greater time detail than seconds, so the millisecond number of datetime values is often zero.
Search : minute
Search based on the minute of the hour number of the datetime values in the source field. For example, the minute of the hour number for 12/25/2016 13:43:53 would be 43.
Search : month
Search based on the month of the year number of the datetime values in the source field. For example, the month of the year number for 12/25/2016 13:43:53 would be 12.
Search : second
Search based on the second of the minute number of the datetime values in the source field. For example, the second of the minute number for 12/25/2016 13:43:53 would be 53.
Search : week
Search based on the computed week of the year number of the datetime values in the source field. Choosing the week option will open a Week start parameter box allowing specification of the day of the week (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, or Saturday) on which a week is considered to begin. Choosing a different Week start day will often change the computed week of the year. For example, 12/25/2016 13:43:53 falls on a Sunday. The week of the year number for 12/25/2016 13:43:53 using a Week start day of Sunday would be 53. Using a Week start day of Monday for that same datetime value would result in a week of the year number of 52.
Search : week day
Search based on the computed day of the week number, from 1 to 7, of the datetime values in the source field. Choosing the week day option will open a Week start parameter box allowing specification of the day of the week (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, or Saturday) on which a week is considered to begin. Choosing a different Week start day will often change the computed day of the week number. For example, 12/25/2016 13:43:53 falls on a Sunday. The day of the week number for 12/25/2016 13:43:53 using a Week start day of Sunday would be 1. Using a Week start day of Monday for that same datetime value would result in a day of the week number of 7.
Search : year
Search based on the year number of the datetime values in the source field. For example, the year number for 12/25/2016 13:43:53 would be 2016.
Search : year day
Search based on the computed day of the year number, from 1 to 365, of the datetime values in the source field. For example, the day of the year number for 12/25/2016 13:43:53 would be 360.