Problems to convert data mon dd yyyy hh:mm

Question

Problems to convert data mon dd yyyy hh:mm

Asked 8 years, 9 months ago

Viewed 412 times

1

I made a select in SQLSERVER inside php:

mssql_query(SELECT TOP 1 ven.DATA FROM tab_venda ven (nolock)
                         INNER JOIN dbo.tab_item_venda iven (nolock)
                                 ON iven.num_Venda = ven.NUM_VENDA
                        INNER JOIN dbo.tab_produto pro (nolock)
                                ON pro.cod_produto = iven.cod_produto
                         WHERE ven.tipo_venda <> 'X'
                              AND pro.Situacao = 1
                              AND (pro.ind_controle_periodo = 1 OR pro.Marca = 'RQ ETIQUETAS')
                              AND ven.COD_PESSOA = @cod_cliente
                              AND pro.cod_produto = @cod_produto
                         ORDER BY ven.DATA DESC)

This SELECT is returning me the date in the following format: Feb 19 2013 12:00:00:000 within PHP. If case I made the same direct select in SQLSERVER Management Studio it returns me in the format: 2013-02-19 00:00:00

The first question would be: Because within PHP the date format is being: Feb 19 2013 12:00:000?

When I try to format the date using:

date('Y-m-d', strtotime($data))

the result being: 1969-12-31

The second question is, why is it returning to date 1969-12-31 instead of 2013-02-19?

2 answers

Browser other questions tagged php sql-server timestamp strtotime

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

by Mastria • **1,133** points · Answer 1 · 2015-09-24T15:34:03+00:00

2

Try to use the function FORMAT in SQL, as the example below:

SELECT FORMAT(ven.DATA, 'dd/MM/yyyy', 'en-US') AS 'data';

Or format in PHP:

$parsed = date_parse_from_format('M d Y H:i:s:B', $data); // 'Feb 19 2013 12:00:00:000'
$new = mktime($parsed['hour'], $parsed['minute'], $parsed['second'], $parsed['month'], $parsed['day'], $parsed['year']);
echo date('Y-m-d', $new);
echo date('d/m/Y', $new);

I hope it helps.

I don’t think that answers either of the two questions.

– Jéf Bueno

2015/09/24 at 16:09
It is an alternative to dealing with the return of her SELECT, maybe it will be useful and solve both questions and maybe it is not, but it is an answer, collaborate with one of yours as well. Hug @Jéfersonbueno

– Mastria

2015/09/24 at 16:40
Yes, that is an alternative I know. But if you pay attention you will see that the questions are: Porque dentro do PHP o formato da data esta sendo: Feb 19 2013 12:00:00:000? and Porque esta retornando a data 1969-12-31 em vez de 2013-02-19?

– Jéf Bueno

2015/09/24 at 16:43
Thank you very much for the help, even trying to format directly in SELECT the return continues in the format: Feb 19 2013 12:00:00:000 I believe it is something linked to the date format on Linux, I guess right.. in a know

– Laísa Ferreira Cardoso

2015/09/24 at 20:12
I edited the answer with the PHP formatting option if you help @Laísaferreiracardoso

– Mastria

2015/09/24 at 22:05

by Diego Souza • **16,524** points · Answer 2 · 2015-09-24T15:47:51+00:00

You can use the function Convert.

SELECT CONVERT(VARCHAR, GetDate(), 103) As Data_Hoje

Will return:

24/09/2015

SELECT TOP 1 CONVERT(VARCHAR, ven.DATA, 103) as Data FROM tab_venda ven (nolock)
INNER JOIN dbo.tab_item_venda iven (nolock)
ON iven.num_Venda = ven.NUM_VENDA
INNER JOIN dbo.tab_produto pro (nolock)
ON pro.cod_produto = iven.cod_produto
WHERE ven.tipo_venda <> 'X'
AND pro.Situacao = 1
AND (pro.ind_controle_periodo = 1 OR pro.Marca = 'RQ ETIQUETAS')
AND ven.COD_PESSOA = @cod_cliente
AND pro.cod_produto = @cod_produto
ORDER BY ven.DATA DESC

You did this above and did as in PHP ?

When you do this, the date is already formatted in format dd/mm/YYYY, not needing so, format by PHP using date or strtotime.