Problems to convert data mon dd yyyy hh:mm

Asked

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

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.

  • 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

  • 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?

  • 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

  • I edited the answer with the PHP formatting option if you help @Laísaferreiracardoso

1

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.

  • Thank you so much for the help, but even if I try to format the date inside the SELECT the return continues in the format: Feb 19 2013 12:00:00:000. I believe it is something related to the Linux server

  • I updated my post.

  • 1

    So that’s the question, even though I format in SELECT the return within PHP is Feb 19 2013 12:00:000 in this format.. even forming. If I do this query outside of PHP, inside Managent Studio for example the right, but when playing inside PHP does not format.. so I think it might be server configuration.. I think

  • You can put in your question how you are doing in PHP ? I don’t think it is the server...

Browser other questions tagged

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