Substring to format date within MAX and MIN

Asked

Viewed 56 times

0

Greetings, I have this query and need to return the maximum and minimum value of a column of a table. These values are date and are written in YYYYMMDD format, I would like to display them in DD/MM/YYYY form and for that I made a substring. However, using the substring within MAX and MIN the query only considers the maximum and minimum values of the day, that is, the result of the query is wrong.

How can I return the maximum and minimum dates already formatted and the full date be considered in MAX and MIN?

Follow the consultation

SELECT 
MT.CODIGO AS MACROTAREFA,
TA.CODIGO AS TAREFA,
ATV.NOME AS ATIVIDADE,
FZ.NOME AS FAZENDA,
TL.NOME AS TALHÃO,
FS.NOME AS SOLICITANTE,
FP.NOME AS PARTICIPANTE,
CASE WHEN TA.SITUACAO = '01' THEN 'ABERTO'
     WHEN TA.SITUACAO = '02' THEN 'EXECUTANDO'
     WHEN TA.SITUACAO = '03' THEN 'PAUSADO'
END AS SITUACAO,
(SUBSTRING(TA.DTREGISTRO, 7, 2)+ '/'+ SUBSTRING(TA.DTREGISTRO, 5, 2)+'/'+ SUBSTRING(TA.DTREGISTRO, 1, 4)) AS 'DATA CRIAÇÃO',
MIN(SUBSTRING(TI.DTREGISTRO, 7, 2)+ '/'+ SUBSTRING(TI.DTREGISTRO, 5, 2)+'/'+ SUBSTRING(TI.DTREGISTRO, 1, 4)) AS 'DATA PRIMEIRO REGISTRO',
MAX(SUBSTRING(TI.DTREGISTRO, 7, 2)+ '/'+ SUBSTRING(TI.DTREGISTRO, 5, 2)+'/'+ SUBSTRING(TI.DTREGISTRO, 1, 4)) AS 'DATA ULTIMO REGISTRO',
DATEDIFF(DAY, (MIN(TI.DTREGISTRO)), MAX(TI.DTREGISTRO)) AS "DURAÇÃO"
FROM TAREFA TA 
LEFT JOIN MACROTAREFA MT ON TA.MACROTAREFA = MT.CODIGO
LEFT JOIN FAZENDA FZ ON MT.FAZENDA = FZ.CODIGO
LEFT JOIN TALHAO TL ON MT.TALHAO = TL.CODIGO
LEFT JOIN FUNCIONARIO FP ON TA.PARTICIPANTE = FP.CODIGO
LEFT JOIN FUNCIONARIO FS ON TA.SOLICITANTE = FS.CODIGO
LEFT JOIN ATIVIDADE ATV ON TA.ATIVIDADE = ATV.CODIGO
LEFT JOIN TAREFAITEM TI ON TI.TAREFA = TA.CODIGO
WHERE FP.DELETED = 0
AND FS.DTDEMISSAO = ''
AND TA.SITUACAO IN ('02','03','01')
AND TA.DELETED = 0
GROUP BY MT.CODIGO, TA.CODIGO, ATV.NOME, FZ.NOME, TL.NOME, FS.NOME , FP.NOME, TA.SITUACAO, TA.DTREGISTRO
sql sql-server date

  • this field is of the type datetime or char/varchar?

  • is of the varchar type

  • 1

    I think you can use a CTE to get Min and Max without formatting, and do the formatting at the end of the query

1 answer

0

Artur, regarding the display of the data field... just put a FORMAT output, example in sql server

SELECT GETDATE() AS 'SEM_FORMAT', FORMAT(GETDATE(),'dd/MM/yyyy','pt-br') AS 'COM_FORMAT';

Now, if the varchar field, according to its argument, should convert first to datatime, here are some examples

SELECT 
 CONVERT(DATETIME, '20210101') AS '1'
,CONVERT(DATETIME, '2021-01-01') AS '2'
,CONVERT(DATETIME, '2021/01/01') AS '3'
,CONVERT(DATETIME, '31/12/2021', 103) AS '4'

or

SELECT DATEFROMPARTS ( 2010, 12, 31 ) AS 'resultado';

After conversion, you can work with the clusters!

  • thanks, it worked out!

Browser other questions tagged sql sql-server date

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