Only display points if today is Monday through Friday.

Question

I have an MXD  that displays open offices based on the  definition query:

Hours.Open < CURRENT_TIME and Hours.Close > CURRENT_TIME

I would like to make it so the points don't display on Saturday and Sunday and only display Mon through Friday (since they are all only open on weekdays).   Is this possible?

This mxd is ultimately used in a web service served by an AGOL map, so if there is a way to filter this in AGOL that would be acceptable.

NinaRihn · Answer

OK so here's what I did...I created a view in my database where there is a record for each office, and fields for the current date, current day of the week, and current time. Then in my MXD, I joined my office locations layer to this view by Office ID, and in my query, I included in my definition query the following: CurrDayOfWeek <> 'Saturday' and CurrDayOfWeek <> 'Sunday' The view definition was: SELECT OFFICE_NO, CURRENT_DATE AS CurrDate, TO_CHAR(sysdate, 'DAY') AS CurrDayOfWeek, ((TO_CHAR(CURRENT_TIMESTAMP,'HH12:MI AM'))) AS CurrTime FROM SDE.FEATCLASSNAME

danielri · Answer

If ArcGIS full supported T-SQL we could just do WHERE DATENAME(weekday, DueDate) <> 'Sunday'

or

DATENAME(weekday,GETDATE()) = 'Thursday'

https://technet.microsoft.com/en-us/library/ms174395(v=sql.105).aspx

If your data is in a database, I would suggest calculating the Day there and save the result into a new field, then use a method similar to the one described above for mapping.

DarrenWiens2 · Answer

I don't think there is an easy way to do this with a definition query. The way you would think this would work would be something like: EXTRACT(DOW FROM CURRENT_TIMESTAMP) >= "FIRSTDAYOPEN" AND EXTRACT(DOW FROM CURRENT_TIMESTAMP) <= "LASTDAYOPEN"‍‍ ...which relies on a column populated for first and last open (Monday = 1 and Friday = 5) and compares the current DOW (day of week) to those values. However, ArcGIS's flavour of SQL only supports YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND (not DOW).