?
Solved

Storing Dates in Databases

Posted on 2014-01-10
6
Medium Priority
?
255 Views
Last Modified: 2014-01-28
Is there a standard for storing dates in databases. I've stored dates as formatted dates, numerical dates, string dates, etc. So I was just curious if one way is better than another way. I typically am only using vba and access.
0
Comment
Question by:jb702
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39773055
Databases will generally have one or more data types specifically geared for dates and/or times.  For example, in SQL Server 2005 and later you have:

datetime
datetime2
date
time

You are generally better off using one of those data types, and then if needed applying any formatting in your presentation layer.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39773082
There is no standard defined for the date storage because we even don't have any standard for date calculation over the world and we are still using several calendars...

The calendar standard is improving continuously and no database which could cover all these past and today's standards exist. And it would be difficult to create it because the calendar used by certain group of people depends on the region, politics, religion etc. You may see the list here: http://en.wikipedia.org/wiki/List_of_calendars

Thus if you are using MS Access then store date into the DateTime field if it covers your needs. Once you decide to use some feature which is not implemented in Access dates (e.g. approximate date etc.) then you have to introduce your own date format (or look around for a solution).

Advantages of existing Date and DateTime data types:
- provide basic calculations and set of date functions
- validate the data entered by users

Disadvantages
- restricted date range and/or time accuracy
- no globally accepted standard is defined
0
 
LVL 32

Accepted Solution

by:
awking00 earned 2000 total points
ID: 39773411
Storing dates as numbers or strings takes away the usage of any date/time functions and requires some type of inefficient conversion to perform any kind of date "math", so it is always best to store them as date/time datatypes.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 62

Expert Comment

by:gheist
ID: 39773815
You probably want to use ANSI SQL:2008 DATETIME (you know it is standard of SQL)
0
 
LVL 27

Expert Comment

by:tliotta
ID: 39774283
I've stored dates as formatted dates, numerical dates, string dates, etc. So I was just curious if one way is better than another way.

In almost all databases, it's better to store as whatever DATE or DATETIME data type is made available by the DBMS. In that sense, none of the ones you listed is a "better way". In most cases, none of them are even "good" ways, though there can be reasons to do it with those.

As noted already, the various 'Date/Time' functions are not available without conversion if some numeric or character data type is used. However, if those functions aren't needed, there can sometimes be little reason to be concerned about it.

'Date' values can be looked at in two kinds of ways.

First, they can need to be handled as "dates", where aspects such as durations or positions in a calendar (day of week, week of year, etc.) are important. If date manipulations are important, then a true DATE data type is called for. You shouldn't need to convert the format to run the functions, nor should there be application code that has to do the work of built-in functions.

But if it's just a kind of identifying element, such as a marker for the creation of a log file or marking the date when some event happened, then it's little more than a tag value. There's often no automating value other than for a sort sequence. In that kind of case, then the better choice can often be whatever the DBMS has that's most efficient for the usage. This type of data is little more than a sequential identifier.

Deciding what is better for any specific database table, though, is difficult. Unless there is a known reason not to, the DBMS-supplied DATE or DATETIME data type should be used.

Tom
0
 
LVL 62

Expert Comment

by:gheist
ID: 39774431
only standard types are datetime and interval
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question