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
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