Symbolizing by year, when date column has MM/DD/YYYY?

Question

I am trying to symbolize an SDE feature, so I can't add any new columns. The date column is formatted as MM/DD/YYYY. I would like to symbolize all 2015s as one symbol, 2016, 2017 as other unique symbols. I figured I could go to unique values and shift click all the relevant rows together and group them, but since month is in front, the sorting just shows all the January months together, so I can't get it to sort nicely before grouping.

Is there a way to symbolize just by date?

Answer 1

I don't have SDE, but since it sounds like you only have a few years, you should be able to select a year and create a layer from that selection. Or just add the SDE feature multiple times and use different definition queries.

Answer 2

I would tackle this using ArcMap's Query Layers to leverage the power of your database. You won't have to export and make and store a derivative layer where it can possibly go stale (needing to be updated once source data changes). Open ArcMap, click File-->Add Data-->Add Query Layer

1. Build the SQL Query (you may need to look up equivalent functions if not using MS SQL Server)

A wizard will open, pick your SDE connection and the database view that represents your feature class. You will then see all the columns for that feature class in the right hand side. From here you can write a SELECT statement to create a new column of just year data. I use Microsoft SQL Server as my database, here is a sample SELECT statement that works in MSSQL:

SELECT 
  Objectid, /* you need a unique identifier, use this one if it already exists */
  Name, /* Add in as little or as many columns you want from the original feature class */
  Date, /* Example where date is stored as text in DD/MM/YYYY format */
  RIGHT(Date,4) as Year, /* we use the RIGHT() function to just take the last four characters */
  created_date, /* Example where date is stored as a datetime column */
  DATEPART(yyyy, created_date) AS Year2, /* We use the DATEPART() function to operate on a datetime column and we can pluck out just the year. */
  Shape /* you need the shape column to display the feature class' geometry */
FROM Maintenance.dbo.PARCELS_VW /* View that represents the Feature Class */

In this statement I want to show you what to do if you your date stored as text or in a date time column. The Date column is my date stored as text, in the format of DD/MM/YYYY. The line after the Date column is where I use the RIGHT() function to trim the last four digits off of the date text and display it as a new column called "Year". The format for using the RIGHT() function is: RIGHT(COLUMN,Number of characters you want to return). If your date is stored in a datetime column, look to the line created_date is on. The line after that is where I use the SQL DATEPART() function to return just the year and display it as "Year2". The format for using the DATEPART() function is: DATEPART(date arguments,COLUMN). Using YYYY for the date argument gives you the four digit year, see https://technet.microsoft.com/en-us/library/ms174420%28v=sql.105%29.aspx for a list of all the valid arguments available. Don't forget to include the FROM clause, this is where you state which table/view (or in GIS speak, feature class) you are going to pull these columns from. Optionally you can add a WHERE clause after the FROM clause to limit what data you pull in. Click next after you have this set up, use the Validate button first to check that the statement is valid.

2. Step through the remainder of the Query Layer wizard

On this next page you will have to choose your unique identifier column (in our case it is Objectid). If you needed to you can select multiple columns to create a composite unique identifier, but for ArcGIS data stored in SDE we can just use the Objectid. Also verify that your geometry is correct (polygons, points, etc) and that the spatial reference it was it should be. Using the SHAPE column in the select statement is what autopopulates this date for you, just verify that it all looks good. Click Finish and the layer is added to your map, as the data is loading a window will pop up saying it is calculating the spatial extent. I usually don't wait for the calculation and just click the button to use the layer's spatial reference. Once in ArcMap you can then open up the properties and symbolize by Year.

Answer 3

I would do the following:

1. Export the table (txt)
2. Open in excel or similar
3. Add column for year
4. Use datepart to parse out the year
5. Delete extra columns (leaving only year and UniqueID)
6. Save as txt.
7. Join back to the feature class in an mxd and symbolize by your new year column.

I believe this should be fairly easy to automate using ModelBuilder to keep all the functions in the ArcGIS Desktop environment. It could then be exported to python and run via a scheduled task if required.

Answer 4

You can make a query layer as suggested. Include a select statement in your definition that somehow converts your date to a numeric (calculate days like iif(x.sampledate is null,999999,DATEDIFF(day, x.SAMPLEDATE, GETDATE())) as 'DAYS', convert to UTC, etc) so you can use values to render. This works in mapserver services and in feature server services. However, joins and relates are not supported nor can you edit a feature service layer when using this approach.

If you are using ArcGIS OnLine or Portal there is a better approach using Arcade. You essentially create an custom attribute expression using date ranges and then symbolize based on that. Here is a link to the relevant article: https://support.esri.com/en/technical-article/000017958