It's very easy as a GIS administrator to add lots and lots of fields to a feature class and even easier to naively think that folks are going to populate or edit them! One common theme for me is date fields. We have edit date, create date, FGDC edit and create date, year...blah blah blah.
I wish there was just one, or even no, onus on editors to have to think about dates and the database just...handled it.
This can be so. With a database trigger. Let's start with FGDC dates. If you're implementing some form of feature-level metadata, or some data-mining that tags another metadata element with FGDC source, create or edit date yet have editor tracking enabled, there's no reason why you should have to also populate the FGDC[whatever]DATE column as well.
CREATE TRIGGER [dbo].[SOMETABLE_DATE]
ON [dbo].[SOMETABLE]
AFTER INSERT, UPDATE NOT FOR REPLICATION
AS BEGIN
SET NOCOUNT ON;
UPDATE [dbo].[SOMETABLE]
SET
SRCDATEFGDC = (convert(varchar(8), SOURCEDATE, 112)),
CREATEDATEFGDC = (convert(varchar(8), CREATEDATE, 112)),
EDITDATEFGDC = (convert(varchar(8), EDITDATE, 112))
END
GO
Here we're taking the EDITDATE and CREATE date values, which are sql
datetime(2)
and converting them to a string format as yyyymmdd. The 112 is what controls the output format. See CAST and CONVERT (Transact-SQL) for a full list of date conversion formats.
In addition, the user is also selecting a source date in this case which may be different than create date and that is converted as well.
If you have a year column you can also
YEAR = CASE WHEN SOURCEDATE IS NULL THEN NULL ELSE YEAR(SOURCEDATE) END
with null value handling thrown in.
If you have editor tracking disabled for some reason (often causes issues with Collector for ArcGIS) you could through a default constraint on your date column(s) as
getdate()
.
This is a personal blog and does not recommend, endorse, or support the methods described above. Alteration of data using SQL outside of the ESRI software stack, of course, is not supported and should not be applied to a production database without a thorough understanding and disaster recovery plan.
