Do not do the date comparison [closed] well

Question

Do not do the date comparison [closed] well

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

2

I am doing a query where I know that my start date is '2017-06-12' and when I compare it I get that it is less than '2017-06-04' , what could it be?

The query is as follows:

select fecha_inicio
      ,month(fecha_inicio) as mes
      ,day(fecha_inicio)
      ,if(fecha_inicio < '2017-06-04', 'MENOR', 'MAYOR') as es
  from ventas
 where numero_cuenta in ('B1557','F3622')
 order by numero_cuenta

If someone could guide me, I would appreciate it, the fields I use are type date .

mysql sql

asked by kany 21.06.2017 в 00:03

source

3 answers

Problem special characters excel - html Align content of 2 div vertically

score 1 · Answer 1

This is how it works:

SELECT 
    fecha_inicio, 
    CASE 
      WHEN fecha_inicio < '2017-06-04' THEN 'MENOR'
      WHEN fecha_inicio = '2017-06-04' THEN 'IGUAL'
      ELSE 'MAYOR'
    END as 'es'
FROM prueba_fechas;

Although I was wondering: what happens if they are the same?

That's why you see that I've added this: WHEN fecha_inicio = '2017-06-04' THEN 'IGUAL' if you're interested in presenting the equality separately, if you're not interested you can delete this line

Note : Be careful not to have your dates formatted in the table, which would be a serious mistake and would cause problems at the time to compare or calculate data.

Complete example

VIEW DEMO

CREATE TABLE IF NOT EXISTS prueba_fechas
    ('fecha_inicio' date)
;
/*  
INSERT INTO prueba_fechas
    ('fecha_inicio')
VALUES
    ('2017-06-01'),
    ('2017-06-02'),
    ('2017-06-03'),
    ('2017-06-04'),
    ('2017-06-05'),
    ('2017-06-06')
;

*/

SELECT 
    fecha_inicio, 
    CASE 
      WHEN fecha_inicio < '2017-06-04' THEN 'MENOR'
      WHEN fecha_inicio = '2017-06-04' THEN 'IGUAL'
      ELSE 'MAYOR'
    END as 'es'
FROM prueba_fechas;

result

    fecha_inicio           es
1   01.06.2017 00:00:00    MENOR
2   02.06.2017 00:00:00    MENOR
3   03.06.2017 00:00:00    MENOR
4   04.06.2017 00:00:00    IGUAL
5   05.06.2017 00:00:00    MAYOR
6   06.06.2017 00:00:00    MAYOR

score 0 · Answer 2

Instead of the if in the select of your SQL statement, you should change it to CASE as follows.

SELECT fecha_inicio
    ,MONTH(fecha_inicio) as mes
    ,DAY(fecha_inicio) as dia
    ,CASE 
        WHEN fecha_inicio < '20170604' THEN 'MENOR' ELSE 'MAYOR'
    END as es
FROM ventas
WHERE numero_cuenta in ('B1557','F3622')
ORDER BY numero_cuenta

Note the way I compare the date, if the start_date field is of the date type, with this you should no longer have problems in your query, regards. p>

score 0 · Answer 3

0

Try putting only in your sql code, it should work. If so, instead of the prints you declare the column and already.

if('2017-06-12' < '2017-06-04')
    print   'MENOR'
else
    print   'MAYOR'

answered by 21.06.2017 в 13:40