Crash Magic Online
  Intersection Magic
  Map Magic
  Knowledge Base
  Contact Us


Knowledge Base Article:CMO200 

Registration keys moved to client area: Select login on the left menu bar.

Search home page  

Recommended date time fields for a collision database

Article created: Sep 29 2008, updated: Apr 20 2010

Background:  Databases offer several different ways to store date and time. This can lead to confusion and in some cases incorrect results when analyzing data. In this article we try to lay out recommendations, and the reason for these recommendations.

Status:  Info - "how to" article
Keywords:  time,datetime,date,null
Categories:   *Installation and setup* *Configuration* *Article - opinion*


Early databases only offered a single data type of datetime to store date and time information. Database store datetime fields internally as binary data. The data is then converted and displayed in a human readable format to the user. The majority of databases store a datetime field as a binary number that represents a number of seconds from a constant datetime.

Problems arise when only a date or only a time is stored in a datetime field. The absence of a date or time will cause the database to use the internal constant date or time. The following examples use MS SQL Server, but you will also find this same issue in Oracle and DB2. An example of a date stored without a time: A date of 4/20/2010 stored in SQL server a datetime field is acctally stored as 04/20/2010 00:00:00. The time is defaulted to midnight. An example of a time with no date: A time of 5:31:49 PM stored in a SQL server datetime field will be stored as 1/01/1901 5:31:49 PM. In this case SQL server has defaulted the date to an internal program constant of 1/01/1901.

Datetime fields do not indicate a null portion of the field. This can skew results when trying to do analysis. A time analysis cannot determine if the time of an item occurred at midnight or the time was never entered.


Modern database versions now have seperate fields for storing date and time information. Some users are not able to use the current database versions and in this case Pd' Programming recommends that two fields be used when storing date and time information. A separate field for storing date information and a separate field for storing time information. Use of two fields allows null data to be preserved for the date and the time. This allows null data to be seen as null data, and not assigned information from the database. 

October 23, 2019 5:17AM

© 1999-2019 Pd' Programming, Inc - Lafayette, CO USA