topic Re: DateTime in Query Layer? in Geodatabase Questions

DateTime in Query Layer?

MeleKoneya — Sat, 12 Jan 2019 22:22:38 GMT

How can I get a Query Layer to show DateTime for the data type instead of Date?

I am wanting to use a query layer to append to an SDE Feature class and if the format is Date, then the append process puts NULL instead of the Date.

I have tried multiple ways in the SQL query in both ArcMap and ArcGIS but each time it shows as a Date Data Type in the Query Layer

This SQL query did not work,

,CAST(DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), CreatedDate) AS DateTime2) AS CreatedDate

Thanks

Mele

Re: DateTime in Query Layer?

JoshuaBixby — Sat, 12 Jan 2019 23:14:02 GMT

I have to think about a solution a bit more, but the short answer is that you can't change the date data type because there is only one ArcGIS date date type: ArcGIS field data types—ArcGIS Help | ArcGIS Desktop .

What DBMS are you using and how are you trying to append the records?

Re: DateTime in Query Layer?

MeleKoneya — Sun, 13 Jan 2019 00:13:02 GMT

Joshua - Thanks for reply. I am using SQL Server 2012 and am using the Append Tool in ArcGIS for the append.

What I am trying to do is take a SQL view that has a Geometry field. and a DateTime field and convert the DateTime values to UTC for use in an Operations Dashboard. I created a Query Layer and within it used DateAdd in SQL to convert to UTC. From there I was going to save the Query Layer to an .lyr, but if I look at the Data Type of the .lyr in ArcMap, it is a Date and not DateTime as defined in SDE Feature Class.

Mele

Re: DateTime in Query Layer?

JoshuaBixby — Sun, 13 Jan 2019 13:31:28 GMT

What version of ArcGIS, both Desktop and enterprise geodatabase, are you using? When you run the append, you say it works but makes all the values NULL?