Should I separate a date into day, month, and year columns?

Question

0.00/5 (No votes)

See more:

I have been trying to redesign portions of each table I have in my database. I have noticed many different structures including just having the full date in one column. However, I have been using the Day, Month, and Year columns so I could easily look for the particular year. Is it more beneficial to make a date column than to have the separate columns?

I appreciate any input, thanks again.

What I have tried:

I have been researching multiple date designs and how a person should be entered into a database, just trying to figure out what is the best for structure purposes.

Posted 28-Sep-18 22:13pm

Member 11856456

Updated 29-Sep-18 16:33pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-09-28T22:51:00

Solution 1

Use a single DATE, DATETIME, or DATETIME2 column if you need a year then it's easy to do: DATEPART[^] will give it to you when you need it.

But separate columns makes it a lot, lot harder to so "all dates after this one", or "ORDER BY <date>" requests.

Always keep values in the appropriate datatype, or it will end up being an pain later. For example, a single column can't contain an invalid date, but it's very easy to make a mistake and swap the day and month numbers with separate columns for example. That leads to massive problems later when you try to use it and find bad dates - and by then it's serious work to fix as you have no idea how many are affected or any idea of the original data!

Posted 28-Sep-18 22:51pm

OriginalGriff

Comments

Member 11856456 29-Sep-18 4:58am

What happens when you have an unknown date or if the date is missing values. For example, 10/--/2017, here we know the month and the year of the event, but not the day. Same can be said of the remaining fields. Maybe someone died from a long time ago and all you can read on the headstone is a portion of the date. would I still be able to use a date column for these cases?

OriginalGriff 29-Sep-18 5:20am

The problem is that you have much the same problem with separate fields, but you have to check for it a whole load more. So for headstones and so forth, you have default values and a separate "notes" column which explains what is estimated, and why.
Without that, you just have "guesses" and no obvious reason regardless of the storage method.

But try doing a "date between 10th July 1921 and 31st Jan 1962" with a "separate columns" system which contains "Don't know" values! That's some nasty code...

Member 11856456 29-Sep-18 22:31pm

I want to say thanks, I adjusted my initial table to structure from the separated values to one single column, which is the birth_date, I will be doing the same thing for death. One of the other issues I have was just searching by year if need be, but I found a way to modify the original code to make it do exactly what I wanted. Again, thanks for the help!

OriginalGriff 30-Sep-18 1:42am

You're welcome!