DateTime in Query Layer?

Question

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

bixb0012 · Answer

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?

MeleKoneya · Answer

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

bixb0012 · Answer

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?