Problems converting data mon dd yyyy hh: mm

1

I made a select in SQLSERVER within 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 the date in the following format: Feb 19 2013 12: 00: 00: 000 within PHP. In case I did select myself direct 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: Feb 19 2013 12: 00: 00: 000?

When I try to format the date using:

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

The result is: 1969-12-31

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

    
asked by anonymous 24.09.2015 / 16:57

2 answers

2

Try using the FORMAT function in SQL, such as 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.

    
24.09.2015 / 17:34
1

You can use the Convert function.

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

Will return:

  

9/24/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

Have you done this above and did like in PHP?

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

    
24.09.2015 / 17:47