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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,
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.
Though input values are in datetime and table column is in datetime i see code like convert(varchar,starttime,
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.
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.
https://msdn.microsoft.com/en-us/library/ms186724.aspx for SQL Server 2008
also make sure to visit the following article by Paul Maxwell:
https://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html