How to get max() and min() data in SQL?

Asked

Viewed 790 times

0

How can I get the minimum and maximum date of a table?

I am using the functions min() and max(), but it is not returning the expected value. This is a small example of the date data from my table:

CREATE TABLE Dados_Vendas
    ([Data] varchar(29))
;
    
INSERT INTO Dados_Vendas
    ([Data])
VALUES
    ('2016-12-02 00:00:00'),
    ('2016/07/16 00:00:00.000000000')
;

If I try to pull the date min() and max() from this table using the conversions:

SELECT 
    MIN(CONVERT(DATE, LEFT(Data, 10), 103)) AS Data_Minima
    , MAX(CONVERT(DATE, LEFT(Data, 10), 103)) AS Data_Maxima 
FROM Dados_Vendas
;

I get a return error: "Conversion failed when Converting date and/or time from Character string."

sql sql-server tsql
  • 2

    What kind of data from your field Data?

  • It’s like sweeping away.

  • 2

    Use the function CONVERT to convert your field to DATETIME type. Probably the string that represents your longest date has a day shorter than 31 or a month shorter than 12. String ordering is different from DATETIME ordering.

  • 4

    If the field is varchar, the result is alphabetical order and the solution is to fix the database. If you have any additions other than this, you can [Dit] your question by providing a problem [mcve] (with structure description). To better enjoy the site, understand and avoid closures and negativities worth reading the Stack Overflow Survival Guide in English.

  • @Bacco edited the question with a small example of the base. If it is wrong, I delete the question.

2 answers

1

The result is wrong because of the field type. You should use date or datetime instead of varchar.

He brings the 31/12/2015 as the maximum value because the 3 is higher value. It sorts the text like this: 0, 1, 2, 3, 4, 5, 6, 7, 8, 9

I believe this may be some legacy system. To bring the right values use the SQL down, where I turn the field to datetime by command convert(datetime, data, 103), where the 103 informs the type of field formatting (Reference of the Convert control), and then convert again to text to display in the pattern dd/mm/yyyy.

select 
  min(data),   
  convert(varchar(10), min(convert(datetime, data, 103)), 103),
  max(data),      
  convert(varchar(10), max(convert(datetime, data, 103)), 103)    
from 
  Dados_Compras

It is available in http://sqlfiddle.com/#! 18/c8d0f/3

Reply edition on 08/04/2020 at 22:39

As the question user comments, the error is occurring Conversion failed when converting date and/or time from character string, this must be happening because it must have invalid date (with day 31 in month that only has 30 days, month above 12 and other formatting). This is because of the use of the type of field unsuitable for date, as I mentioned in my reply.

To find the wrong records, run the SQL below. I also updated the example. New link Updated response in SQL Fiddle

select 
  * 
from 
  Dados_Compras 
where 
  try_convert(datetime, data, 103) is null

  • I tried to convert the data type, but it returns an error "Conversion failed when Converting date and/or time from Character string."

  • 1

    @Alineat updated the answer and updated the example in sql fiddle.

0

Convert the field to the type DATE:

SELECT 
    MIN(CONVERT(DATE, LEFT(Data, 10), 103)) AS Data_Minima,
    MAX(CONVERT(DATE, LEFT(Data, 10), 103)) AS Data_Maxima 
FROM Dados_Compras

  • I tried this, but it returns an error: "Conversion failed when Converting date and/or time from Character string."

  • 1

    Try using LEFT to pick up only the date characters without considering the time: LEFT(Date, 10), I changed in the answer for you to test.

  • I tried with LEFT and returns the same error "Conversion failed when Converting date and/or time from Character string."

  • 1

    If you use the wrong kind and ordinary things get complicated...

Browser other questions tagged sql sql-server tsql

You are not signed in. Login or sign up in order to post.