topic Re: Date Field Issues in Data Management Questions

Date Field Issues

Anonymous User — Wed, 31 Jan 2018 15:58:58 GMT

Good Morning,

Having difficulty with a SQL database Date Field. I have a view created to extract data used for a Web Map. All the data comes over on the view except the date field? My in house people have helped me to convert the Date Field in the view to a string and it now shows in my view copy in ArcGIS Catalog, but when using this field in Web AppBuilder, of course it doesn't show as an actual Date field, limiting my functionality. Is there a way of converting this String "Date" field to an actual "Date" field in ArcGIS so the Web Map views it as such? I have tried the Modelbuilder tool Convert Date Time, and I am looking at the Calculate Field Tool now, but not very good with VB? Any help would be greatly appreciated.

Thanks

Re: Date Field Issues

JoshuaBixby — Wed, 31 Jan 2018 16:20:11 GMT

First, VB, don't bother. Start learning Python, JavaScript, and (I hold my nose) Arcade.

Second, providing some more software information is helpful. For example, does "SQL database" mean SQL Server or are you using some other kind of DBMS? What client software? What versions of server and client software?

Re: Date Field Issues

DanPatterson_Retired — Wed, 31 Jan 2018 16:24:12 GMT

Date field info...

http://pro.arcgis.com/en/pro-app/help/data/tables/date-fields.htm

and the sql query section has info as well

http://pro.arcgis.com/en/pro-app/help/mapping/navigation/sql-reference-for-elements-used-in-query-expressions.htm

sadly... another 'standard' has so many standards, it is hard to keep track of them

Re: Date Field Issues

Anonymous User — Wed, 31 Jan 2018 16:32:16 GMT

In our Enterprise we use Oracle 11.2 our client uses MS SQL Server 2014 standard issue. All of our ESRI software is 10.5.1. I'm not married to VB as I'd prefer to use Python, but I was just looking into the Calculate Field tool and I'm not sure this is the best way? We are running these FC's on our hosted web services and the tool also fails when the service is running. I am trying to automate this whole process.

Thanks

Re: Date Field Issues

Anonymous User — Wed, 31 Jan 2018 16:36:19 GMT

Thanks for the reply, We are using ArcGIS Desktop as we haven't really got into Pro yet. I know some of these functions overlap though. This may be my own lack of knowledge.

Re: Date Field Issues

DanPatterson_Retired — Wed, 31 Jan 2018 16:41:05 GMT

Steven, there is overlap and equivalent ArcMap pages, things are just easier to find in the Pro help and if there is a difference with *Map, it is usually indicated on their pages.

Re: Date Field Issues

Anonymous User — Sat, 11 Dec 2021 09:43:28 GMT

This is the View script that I have been using. It was written by our customer and the Field for Date is a Date Field in SQL. If this helps. Thanks,

SyntaxEditor Code Snippet

create view v_pc_citations_by_address as 
SELECT CaseViolation.CaseID as 'CaseNumber',
CaseViolation.ViolationNumber as 'CitationNumber', 
CaseViolation.ItemCode AS Code, 
CASE CaseViolation.SystemViolationTypeID WHEN 1 THEN 'Citation' ELSE 'Warning' END AS [Type], 
CASE CaseViolation.ItemCode When '38(I)' Then 'Irresponsible Owner' When '38(II)' Then 'Irresponsible Owner' When '38(IV)' Then 'Irresponsible Owner' When '39' Then 'Bite Incident' when '14-39' Then 'Bite Incident' When '29(a)' Then 'Pet Dealer/Hobby Breeder/Kennel' When '29(f)' Then 'Pet Dealer/Hobby Breeder/Kennel' When '29(g)' Then 'Pet Dealer/Hobby Breeder/Kennel' When '61(f)' Then 'Pet Dealer/Hobby Breeder/Kennel' When '30' Then 'Public Nuisance' When '33(a)' Then 'Public Nuisance' When '33(b)' Then 'Public Nuisance' When '34(a)' Then 'Animals in Vehicles' When '34(b)' Then 'Animals in Vehicles' When '35(a)' Then 'Cruelty/Neglect' When '35(b)' Then 'Cruelty/Neglect' When '35(c)' Then 'Cruelty/Neglect' When '32' Then 'Cruelty/Neglect' When '32(b)' Then 'Cruelty/Neglect' When '35(d)' Then 'Cruelty/Neglect' When '35(e)' Then 'Cruelty/Neglect' When '35(f)' Then 'Cruelty/Neglect' When '46(b)' Then 'Obstruction' When '46(c)' Then 'Obstruction' When '48(f)' Then 'Quarantine Violation' When '61(a)' Then 'License Violation' When '62(c)' Then 'Guard Violation' When '62(d)' Then 'Guard Violation' When '62(e)' Then 'Guard Violation' When '62(f)' Then 'Guard Violation' When '62(g)' Then 'Guard Violation' When '63(a)' Then 'Animal at Large' When '63(c)' Then 'Animal at Large' When '64(g)' Then 'Dangerous Dog Violation' When '31(c)' Then 'Tethering' END AS 'ViolationType', 
CONVERT(date, CaseViolation.DateIssue, 102) AS [IssueDate],
refViolationResult.ViolationResult as FinalResult ,
CONCAT(Person.NameFirst,' ',Person.NameLast) as 'OffenderName', 
CONCAT(PersonAddress.StreetNumber,' ',PersonAddress.StreetDirection,' ',PersonAddress.StreetName,' ',PersonAddress.StreetType,' ',PersonAddress.StreetDirection2,' ',PersonAddress.City,' ',PersonAddress.PostalCode) as 'FullAdd' 
FROM CasePerson INNER JOIN CaseViolation ON CasePerson.CasePersonID = CaseViolation.CasePersonID 
INNER JOIN refAddressType 
INNER JOIN PersonAddress ON refAddressType.AddressTypeID = PersonAddress.AddressTypeID 
INNER JOIN Person ON PersonAddress.PersonID = Person.PersonID ON CasePerson.PersonID = Person.PersonID 
Inner Join refViolationResult on CaseViolation.ViolationResultID = refViolationResult.ViolationResultID‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Date Field Issues

BlakeTerhune — Wed, 31 Jan 2018 16:58:58 GMT

We've published an Oracle view to an ArcGIS Online Web Map with date fields and the only problem was the browser assuming it was in UTC and converting it to local time. We converted the date to a string so it wouldn't try to change time zones. We just used TO_CHAR()

TO_CHAR(myDateField, 'YYYY-MM-DD') as myDateField‍

Re: Date Field Issues

Asrujit_SenGupta — Wed, 31 Jan 2018 19:40:45 GMT

Make sure you are using a supported Date - data type.

SQL Server data types supported in ArcGIS—Help | ArcGIS Desktop

ArcGIS data types SQL Server data types created Other SQL Server data types that can be viewed Notes
BLOB
VARBINARY(MAX)
BINARY, IMAGE, TIMESTAMP, VARBINARY(n)
DATE
DATETIME2(7)
DATETIME2(n), DATETIME, SMALLDATETIME

ArcGIS data types	SQL Server data types created	Other SQL Server data types that can be viewed	Notes
BLOB	VARBINARY(MAX)	BINARY, IMAGE, TIMESTAMP, VARBINARY(n)
DATE	DATETIME2(7)	DATETIME2(n), DATETIME, SMALLDATETIME