Date Field Issues

431
8
01-31-2018 07:58 AM
Highlighted
New Contributor II

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

Reply
0 Kudos
8 Replies
Highlighted
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?

Highlighted
New Contributor II

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

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

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

Reply
0 Kudos
Highlighted
New Contributor II

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.

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

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.

Highlighted
New Contributor II

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
Reply
0 Kudos
Highlighted
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
Highlighted
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

Reply
0 Kudos