Date Field Issues

1523
8
01-31-2018 07:58 AM
by Anonymous User
Not applicable

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

SC

0 Kudos
8 Replies
JoshuaBixby
MVP Esteemed Contributor

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?

by Anonymous User
Not applicable

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

0 Kudos
DanPatterson_Retired
MVP Emeritus

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-ex...

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

0 Kudos
by Anonymous User
Not applicable

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.

0 Kudos
DanPatterson_Retired
MVP Emeritus

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.

by Anonymous User
Not applicable

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
0 Kudos
BlakeTerhune
MVP Regular Contributor

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
Asrujit_SenGupta
MVP Regular Contributor

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

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

ArcGIS data typesSQL Server data types createdOther SQL Server data types that can be viewedNotes

BLOB

VARBINARY(MAX)

BINARY, IMAGE, TIMESTAMP, VARBINARY(n)

DATE

DATETIME2(7)

DATETIME2(n), DATETIME, SMALLDATETIME

0 Kudos