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