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
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?
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
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
sadly... another 'standard' has so many standards, it is hard to keep track of them
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.
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.
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
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
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