I have date and time data in two field in a csv file that I will be appending to a SDE feature class. I have created two fields in the target feature class for the data--date and time--and set the data type to date for both. One goal we have is for the data to be sortable by the time field. I have tested it in ArcGIS 10.1 and it looks like it is working fine. However, when I look with a database tool I see that the time records have a weird date added to them, which I am guessing is just a esri place holder for the date.
Screeshot from TOAD
So, is this the best practice for storing time? We are on: SDE 10.1 oracle11g
Solved! Go to Solution.
I have a habit of storing datetime in a text format, as it seems every database and computer platform has a different binary datetime format, and some (INFO) don't support time. When you need to do a query, or a date time calculation, you can do it easily if it is in string format, no matter where you are doing it. Also, the datetime won't get scrambled when you copy the table to a format that stores it differently (for example Oracle to .dbf).
I agree you should not separate date and time, whether you go with a binary or text format.
In situations like that, I treat the datetime string as the real data and would derive the time from it (either by calculation into a derived field or using SQL functions).
Ya, that would not be so great because when we go to export the stuff out of sde for reports we would have to change it back to the original format. Which feels like unnecessary overhead. I don't know I am inclined to just use it as it is. As it is displaying properly in Arc. Also, it is sorting properly in arc and when using SQL with TOAD outside of the the arc environment. If it is ever a problem having the "holding" date in the table we can just query it out on the backend.