datetime columns in table

What is the best practice for storing dates in DB. I am going to perform lot of calculations based on dates for report generation.
So i do not want to convert them from one format to another for comparisons and causing performance issues.

If you can point me to any links is also appreciated. Thanks for your time.
JyozealAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
The way dates are store in a database is totally independent of its format. In designing your table, if you will not be concern with hh;nn;ss, the used data type Date otherwise use DateTime there are some other options at:

https://msdn.microsoft.com/en-us/library/ms186724.aspx for SQL Server 2008

also make sure to visit the following article by Paul Maxwell:

http://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html
Jim HornMicrosoft SQL Server Data DudeCommented:
>So i do not want to convert them from one format to another for comparisons
You'll have to spell out for us exactly what this means.

For 'convert', in addition to the Paul Maxwell article here's an articled called Build your own SQL calendar table to perform complex date expressions that has all the code ready to execute to build a table where each day is a row, and there are multiple columns pre-calulated for different formats.  So, instead of having to calculate every time in your T-SQL, you can just join on this table and grab the desired column.

For 'comparisons' between two dates, the above article will work for business days, but you'll have to give us more details.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Use datetime data type since you'll make calculations (difference of dates, date add, ...). You also have some other datatypes that you can use depending on the precision that you want (only date, only time, second precision, millisecond precision, UTC date format, etc...).
Just try to not use strings since then you'll need to convert them and then you won't be able to use indexes on those fields.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Scott PletcherSenior DBACommented:
Use a data type of "date" if you don't need a time component, like this:
CREATE TABLE table1 ( column1 date NOT NULL )

Btw, do not use just an integer, like this:
20150526
because you would have to convert it to a date to do any calculations.
JyozealAuthor Commented:
Thanks for all your comments. In my current work i get start date and end date as input values and i retrieve all rows between those dates. In similar fashion there are many stored procedures that give data based on date calculations like no of users registered in last one month/quarter  for each role in the system. Compare the registrations between weeks like last week to this week for weekly report. Similar way for payments.

Though input values are in datetime and table column is in datetime i see code like convert(varchar,starttime,112) between convert(varchar,input starttime,112) and convert(varchar,input endtime,112). I have time included in all columns. But i dont need time in some cases ,i just need date. However i cannot change table definitions. This is running slow and index on starttime is not used eventhough its available because of convert function as highlighted by Vitor Montalvão.

When i was going through forums i came across that storing date as ISO-8601 is best. ScottPletcher, Can you please explain why i should not store that way?  Do you mean to use it as string '20150526' ?

eghtebas, in future designs(i have one or 2 tables to create new) i am going to just use Date only as suggested. Links were helpful.

Jim Horn, i went through the links. i will go thru thoroughly little later. I think that will help me a lot i guess. Because i need business days etc. However i have 1 qtn. Can we enter calendars for 2 organizations in the calendar table ? Each org might have a different calendar like holidays etc.
Scott PletcherSenior DBACommented:
Storing dates as a "date" type, or datetime type, is best.  If for some reason you insist on not using date or datetime, then store them as an int in format YYYYMMDD with the appropriate check constraints to make sure it's a valid date.  You should never store date or datetime values as a string except when exporting the data if needed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.