DateTime in Query Layer?

274
3
01-12-2019 02:22 PM
Highlighted
Regular Contributor

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

Tags (1)
Reply
0 Kudos
3 Replies
Highlighted
MVP Esteemed Contributor

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?

Reply
0 Kudos
Highlighted
Regular Contributor

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

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

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?

Reply
0 Kudos