Problem with select in datetime field

Question

Problem with select in datetime field

Navigation

#1 by (3 votes)

1

The definition of the intento table is:

create table INTENTO
(
   ID_INTENTO           int not null auto_increment,
   ID_U                 int,
   ESTADO               varchar(50) not null,
   FECHA                datetime not null,
   primary key (ID_INTENTO)
);

Testing the query:

SELECT i.'ID_INTENTO', i.'FECHA' from intento i

Return me:

So far so good. Now what I'm trying to do is make another query that depends on a specific date and get the id_intento and fecha of the table intento :

SELECT i.'ID_INTENTO', i.'FECHA'
  from intento i  where i.'FECHA'='30/01/2017 02:39:07 a.m.'

Does not return any results.

What am I doing wrong? Why is it empty if that date exists? (see the image).

mysql sql

asked by hubman 02.02.2017 в 08:12

source

1 answer

Database concepts How to copy the contents of one folder to another?

score 3 · Answer 1

That graphic client you're using is cheating to show you localized dates and times.

In the database the dates have to be indicated in YYYY-mm-dd HH:MM:SS format (among other formats) or else you will not get any match.

For example, to obtain the registration correctly, the query would be:

SELECT
  i.ID_INTENTO, i.FECHA
FROM
  intento i
WHERE
  i.FECHA = '2017-01-30 02:39:07'

Here you can find more information: 10.1.3 Date and Time Literals

MySQL recognizes DATETIME and TIMESTAMP values in these formats:


As a string in either 'YYYY-MM-DD HH: MM: SS' or 'YY-MM-DD HH: MM: SS'   format A "relaxed" syntax is permitted here, too: Any punctuation   character may be used as the delimiter between date parts or time   parts. For example, '2012-12-31 11:30:45', '2012 ^ 12 ^ 31 11 + 30 + 45',   '2012/12/31 11 * 30 * 45', and '2012 @ 12 @ 31 11 ^ 30 ^ 45' are equivalent. The only delimiter recognized between a date and time part and a   fractional seconds part is the decimal point. The date and time parts can be separated by T rather than a space. For   example, '2012-12-31 11:30:45' '2012-12-31T11: 30: 45' are equivalent.



As a string with no delimiters in either 'YYYYMMDDHHMMSS' or    'YYMMDDHHMMSS' format, provided that the string makes sense as a date.   For example, '20070523091528' and '070523091528' are interpreted as   '2007-05-23 09:15:28', but '071122129015' is illegal (it has a   nonsensical minute part) and becomes '0000-00-00 00:00:00'.



As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided   That the number makes sense as a date. For example, 19830905132800 and   830905132800 are interpreted as '1983-09-05 13:28:00'.