Problem with date in SQL

• You are storing the date as a string of characters, not as a date, of your statement create table

    

  fecha_venta varchar(100) not null,

  The data type is clearly visible, varchar . With this, the engine is completely unaware that the stored data is a date (or anything else). It will treat it like any other string of characters.

• From the select that you show at the end, you see that the date stored is '3-12-2018'

• Your search is done by a string of characters, but the date has a different format: '03 -12-2018 ', and therefore the engine will not find any match.

Having said that, database engines have the ability to handle date, time and date and time types of data. Storing them as such has the advantage of being able to do arithmetic of dates at the level of the database engine, in addition that the comparisons will always be on the datum and not on its visual representation , as you have faced in this case.

In the specific case of postgresql , according to the documentation , you have the following types of data:

• timestamp , date and time (with or without time zone)
• date , only date
• time , only hour
• interval , is a time interval

My recommendation is that you store your data in these, and not as chains. It is the equivalent of storing a number as a string or as a number.

you should change the field from date to date:

create table detalle_ventas ( 
fecha_venta date not null,
codigo_barra int not null,
descripcion varchar(50) not null,
cantidad double precision not null
)

Your SQL has two problems:

• You do not use the specific field type for dates date .
• In case of using a field of type text ( varchar , text , etc) you are not using the basic or extended format of date ISO-8601 .

The alphabetical order does not work with the dates as you are using them. As a result:

• 03-12-2018 is different from 3-12-2018 :
  • In particular 03-12-2018 is a date before 3-12-2018 because the first character of the first date ( 0 ) is less than the first character of the second ( 3 ).
• 12-12-2019 is a date before 23-12-2018 .
  • This is because the first character of the first date ( 1 ) is lower than the first character of the second ( 2 ).

By not using the field type date you do not get an error when inserting dates that may be wrong in format or content and by not using the recommended ISO format the alphabetical (or alphanumeric) order does not match the order of dates.

As you have defined the schema, you could use text fields using the format AAAA-MM-DD (4-digit year, 2-digit months, and 2-digit days, all with fill zeros to the left if necessary) :

INSERT INTO detalle_ventas (
  fecha_venta, codigo_barra, descripcion, cantidad
) VALUES (
  '2018-12-03', 0, 'descripcion', 1
);

Both SELECT would work correctly.

But I, personally, would recommend you to pass the field to the guy I have recommended:

ALTER TABLE detalle_ventas
    ALTER COLUMN fecha_venta TYPE date USING fecha_venta::text::date;

Or the full definition:

CREATE TABLE detalle_ventas (
  fecha_venta date not null,
  codigo_barra int not null,
  descripcion varchar(50) not null,
  cantidad double precision not null
);

You can see a proof of concept online at this link .