Dates, Times, and Databases

From: Matt Hughes (hughes.matt_at_gmail.com)
Date: 11/05/04 

Date: 5 Nov 2004 08:17:54 -0800

I have recently been asked to add a datetime column to my database
schema and support searching on it through a GUI. My problem is that
sometimes people will enter just a date in the column, sometimes a
date with just the hour, and sometimes maybe they'll want to enter all
the datetime information down to the millisecond.

Of course, if someone enters in the search field just a date
'11/04/2004' they want all the rows in that table that match that date
regardless of time. However, if they get more specific, such as
'11/04/2004 2:00' they want all the rows that match that date and time
regardless of the trailing time units (seconds, milliseconds).

However, that is not how databases currently work with datetime in my
experience. If you have a datetime field and the entry is '11/04/2004
2:00 PM' and try to search where field='11/04/2004' it will not match
that entry. Has anyone dealt with this situation and created any sort
of solution. It seems that databases should be much smarter about
this.

Relevant Pages

  • Re: Dates, Times, and Databases
    ... > the datetime information down to the millisecond. ... > regardless of the trailing time units ... It seems that databases should be much smarter about ... Another idea would be to use your own implementation of data columns as ...
    (comp.lang.java.databases)
  • RE: Field mapping on Importing
    ... > I regularly import data from MS access databases to ... > SQL server using the enterprise manager. ... I click on every table and select the DateTime ... > methods for importing very different databases into my one I would love to ...
    (microsoft.public.sqlserver.programming)
  • Re: Data grouped by 20 minutes?
    ... Assuming the time you have in each record is a datetime, ... this example assumes that you have a table called Accounts with a datetime ... This seems to work on one of my databases. ... Basically works by converting datetime to an integer and then grouping on ...
    (comp.databases.ms-sqlserver)
  • Re: adding time segments
    ... It seems to me that the most straightforward implementation would be to keep some kind of list of "DateTime with range" instances. ... DateTime _dtEnd; ... If there's overlap, you would modify the existing entry in your list, expanding it to include the new entry. ... The overlap could involve the new range ending within an existing range, beginning within an existing range, being completely contained within an existing range, completely containing an existing range, or even spanning two existing ranges. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: ids 10 datetime migrate error
    ... the datetime column is not null ... Create table x(log_date datetime year to second not null) ... ids: 10.00.FC3R1 server name: on17tcp ...
    (comp.databases.informix)