4
I always save my bank dates using the type datetime. When displaying these dates in an application, I do something like:
select Convert(Varchar, GETDATE(),103) Data
Upshot: 04/10/2016
I’d like to understand why we use datetime instead of varchar? In that case I wouldn’t need to use a Cast in the user view!
Some performance implication involved?
Obs: If possible give examples.
6
The problem of using a date field of how varchar it’s time to order,
CREATE TABLE `datas` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_varchar` varchar(45) DEFAULT NULL,
`data_date` date DEFAULT NULL,
`data` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1
Inserts:
INSERT INTO `datas` VALUES (1,'30/01/2015','2015-01-30',NULL),
(2,'29/01/2015','2015-01-29',NULL),
(3,'30/03/2015','2015-03-30',NULL),
(4,'29/03/2015','2015-03-29',NULL),
(5,'28/04/2015','2015-04-28',NULL),
(6,'28/02/2015','2015-02-28',NULL),
(7,'27/02/2015','2015-02-27',NULL),
(8,'31/12/2015','2015-12-31',NULL),
(9,'30/12/2015','2015-12-31',NULL)
I used Mysql as an example (guess question shows SQL Server) also works.
Ordination as data_varchar see the days come first independent of the month (lines 2 and 3)
Consultation:
select data_varchar, date_format(data_date, '%d/%m/%Y') from datas order by data_varchar
Upshot:
data_varchar|data_date
27/02/2015 |27/02/2015
28/02/2015 |28/02/2015
28/04/2015 |28/04/2015
29/01/2015 |29/01/2015
29/03/2015 |29/03/2015
30/01/2015 |30/01/2015
30/03/2015 |30/03/2015
30/12/2015 |31/12/2015
31/12/2015 |31/12/2015
Ordination with data_date see that now the date was ordered as the expected first the lowest day of the month (29/01) and the next (30/01) and then moved to the next month (02).
Consultation:
select data_varchar, date_format(data_date, '%d/%m/%Y') from datas order by data_date
Upshot:
data_varchar|data_date
29/01/2015 |29/01/2015
30/01/2015 |30/01/2015
27/02/2015 |27/02/2015
28/02/2015 |28/02/2015
29/03/2015 |29/03/2015
30/03/2015 |30/03/2015
28/04/2015 |28/04/2015
31/12/2015 |31/12/2015
30/12/2015 |31/12/2015
That is, if you prefer to use VARCHAR instead of DATETIME, don’t ask me to work on this project after, I’m out :p
@Wallacemaxters exactly that, da para contonar isso ... already edited.
@rray of great help to your answer, just wanted to understand that this only applies to ordering?
@Wallacemaxters already use the datetime rs. :)
4
On the face, the first advantage I can talk about DATETIME is that when comparing intervals between dates, you waive the use of functions to make conversions to find a result.
Example
SELECT * FROM usuarios WHERE date >= '2015-10-02 00:00:00' <= '2017-12-02 00:00:00'
Another advantage is that usually the DBMS that allows you to define the type DATETIME will not allow a date to be entered wrongly. Of course, it will depend on how it is also configured.
Now, if you want to use VARCHAR instead of DATETIME, get ready:
You cannot easily add/subtract days to the VARCHAR version.
It is harder to extract only month/year.
There is nothing to prevent you from placing the data non-date in the VARCHAR column in the database.
You can’t easily sort by dates.
It’s hard to change the format if you want it later. (The Dbms have specific functions to work with dates)
It’s unconventional, which will make it harder for other developers to understand what you’ve done.
This will affect the disk size used. VARCHAR will use more storage space. This may not matter for small amounts of data, but in commercial environments with millions of data lines this could make a big difference.
Thank you for the @Wallace Maxters reply.
You’re welcome, @Marconi. We’re here to help :)
Browser other questions tagged sql date
You are not signed in. Login or sign up in order to post.
http://stackoverflow.com/questions/4759012/when-to-use-varchar-and-date-datetime. (Do not read only the first or most voted reply. Read all)
– Daniel Omine
It is important to understand that when you save a value in datetime, usually the data is not saved verbatim. It is not saved
2016-10-04 00:00:00, but the numerical equivalent representing the date and time, which is much more space and speed efficient for calculations and comparisons.– Bacco
@Danielomine I will finish reading and give my opinions here.
– Marconi
@Bacco does not want to create an answer?
– Marconi
@Marconi the answers given I think are already comprehensive enough, I just complemented to emphasize this point.
– Bacco
@Bacco I understood, I did not know that the date was represented by a number, it would be something like a Bite?
– Marconi
@Marconi In Myisam, if I’m not mistaken, 12 bits are used for the year, 4 bits for the month, and 8 bits for the day, totaling 3 bytes (for the date part). Other Engines can store as an absolute number in seconds, from a given date, for example. There it varies from implementation to implementation. There are systems that use float, the fractional part being the hours, and the integer the number of days of a reference date (for example, 283.5 would be half a day, and 285 days after the initial reference date). Sqlite itself does not have a dedicated type of date, but has timestamp functions for conversion
– Bacco