How to convert date to date in select query?

Asked

Viewed 718 times

0

Follow the picture below:

inserir a descrição da imagem aqui

Follows the code:

select dt_abertura, count(1) as qtd
from tabela
group by dt_abertura;

How to convert date to time (2017-09-23 16:24:55) for date only (23-09-2017) ?

I would like to be able to group these figures in order of date example:

01-01-2017 Next down 02 -01- 2017 e por ae vai

mysql

  • You want to show only the date at select time ?

  • Yes, and manage to group the amount of her records

  • 1

    Try that: https://stackoverflow.com/questions/10637581/mysql-date-format-dd-mm-yyyy-select-query

  • 2

    Look at Tony’s answer and don’t forget to give.

  • http://prntscr.com/jvebxy

  • Francisco comments on this in Tony’s reply comment below.

Show 1 more comment

3 answers

3

I got it just the way I wanted it:

SELECT DATE_FORMAT(dt_abertura,"%d/%m/%Y") AS Data, count(1) as qtd
FROM tabela
group by DATE_FORMAT(dt_abertura,"%d/%m/%Y")
order by date(dt_abertura);

See the result:

resposta

Thank you to everyone who tried to help me.

  • 1

    Put your code, this link could break one day and no one will have access to the code!

  • New here malz :)

  • 2

    It’s worth a look https://answall.com/tour

1

Try it like this:

select DATE_FORMAT(dt_abertura, '%Y/%m/%d'), count(1) as qtd
from tabela
group by DATE_FORMAT(dt_abertura, '%Y/%m/%d');

Docs: DATE_FORMAT.

  • 1

    http://prntscr.com/jvelta n did not work here, the result I expected was the following : http://prntscr.com/jvemcz the numbers in front would be the quantity .

  • 1

    Try using the DATE_FORMAT

0

Tested with Mysql 5.7.12:

SELECT CAST(ROUND(CAST(CAST('2018-06-15 17:22:00' AS DATETIME) AS UNSIGNED) / 1000000) AS DATE);

In your case, it would be

SELECT CAST(ROUND(CAST(dt_abertura AS UNSIGNED) / 1000000) AS DATE) AS data, COUNT(*) AS qtd
FROM tabela
GROUP BY 1
ORDER BY 1 DESC;

Browser other questions tagged mysql

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