Customizing DateTime field in ArcPy data management feature

Question

I have a table in SQL with one column TIMESTAMP which shows the date/time in the following format.

2019-05-31 08:09:20.000

In order to process the data in ArcGIS, I created a new table in ArcGIS. To bring the Date/Time column I used the following code in Python.

arcpy.AddField_management(Track,'DT_start','DATE')  # Add timing fields (Date/Time fields)

I get the date/time feature in "DT start" column in "Track" table in the following format.

31/05/2019 08:09:20

When I load the output table back to SQL I get date/time in the following format.

2019-05-31 08:09:20.0000000

How do I set the format in python code so that I get the date/time format in the same format as in the original table in SQL?

I would like to see the same format not only in ArcGIS output table but also when I load it back to SQL.

Is there any difference in the date/time format in ArcMap and ArcGIS Pro?

Answer 1

A date stored in the database does not have a format, it is just a... date. What you see 'in SQL' is just a text representation of that date, which format may be determined by settings in the the database itself, or by whatever application you use to query the database. But that's just what you see on screen.

Both ArcMap and ArcGIS Pro use the date format settings from your user settings in Windows. As far as I know, this cannot be changed for individual fields in ArcGIS. (it seems this is possible in Pro 2.7)

If you really must have a specific format stored in the database, then the only option would be to use a text field rather than a date field. I do not recommend this.

If you want to use arcpy to show a date, you can use the format function to specify your favorite format, e.g.

d = datetime.now()
print("{0:%Y-%m-%d %H:%M:%S.%f}".format(d))

# prints 2021-10-14 08:49:40.523000

Answer 2

These are the same values (as long as their Data Types are Date), and if I were you, I would not be worried about how they are presented if it is not really important.

How ArcGIS uses the date field formats is explained in https://desktop.arcgis.com/en/arcmap/10.3/manage-data/tables/fundamentals-of-date-fields.htm, where it says:

Settings on your Windows system determine how the dates are displayed in ArcMap—M/d/yy, MM/dd/yy, yy/MM/dd, and so on. ArcMap uses the system short date format (numerical) for displaying dates.

How SQL tables are treated in ArcMap explained in this page and running date field queries on different data sources discussed in other questions in GIS-SE, e.g. https://gis.stackexchange.com/a/54246/28687

If you insist, you can create a Text field and calculate dates as string by using built-in datetime formatters of python, e.g.

datetime.datetime.strptime(!DT_start!, '%d/%m/%Y %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S.%f')