PHP (Between + Convert Date)

Asked

Viewed 330 times

0

I’m making a screen in php where the user has the date filter to extract a report from the database SQL Server, but the Mysql displays the default date mm/dd/yyyy, and I need him to turn dd/mm/yyyy in the between, someone knows how to make this conversion?

Follow the way I tried:

SELECT p.idProcesso, p.Usr_id, p.Usr_name
  , p.Usr_CPF, p.crd_snr, t.dsPunicao
  , convert(varchar, p.dtCadastro, 103)+' '+convert(varchar, p.dtCadastro, 108) dtCadastro
  , s.dsStatusProcesso
  , p.idStatusProcesso 
FROM tmProcesso AS p
INNER JOIN tcStatusProcesso as s 
   on p.idStatusProcesso = s.idStatusProcesso
INNER JOIN tcPunicao as t 
   on p.idPunicao = t.idPunicao
WHERE p.app_id = '$var_appid'
  and p.dtCadastro between CONVERT(VARCHAR, '$var_dtini', GETDATE(), 103) 
  and CONVERT(VARCHAR, '$var_dtfim', GETDATE(), 103)"

I don’t know if this way is right, I’d like to confirm, if it’s too obvious, forgive me.

php sql-server

  • In Mysql and PHP I use this way for date queries, and it works: DATE_FORMAT(r.DATA_SAIDA, '%d-%m-%Y') the date, where the date in the database is as mm/dd/yyyy and select I turn to dd/mm/yyyy

  • @Lidianefernandes Is it Mysql or SQL Server? You quote Mysql in the text but the functions are SQL Server. // How is the column declared p.dtCadastro? // The format of $var_dtfim is dd/mm/yyyy?

  • @Josédiz is SQL Server, MSSQL. Its database format is mm/dd/yyyy

  • Fixing, up there appears Mysql even, but it is MSSQL.

2 answers

1

Guys, I ended up poking around a little bit more and it worked out in a simple way, I’ll put it here in case someone also has the doubt:

   p.dtCadastro between convert(datetime, '$var_dtini', 103)
   and convert(datetime, '$var_dtfim', 103)

Personal thank you!

1

You can convert the date before moving to the query. for example:

function convertDatetoSQL($date)
{
    $date = explode('/', $date);
    return $date[1] . '/' . $date[0] . '/'. $date[2];
}

$var_dtfim = convertDateToSQL($var_dtfim);

With the explodeyou transform the variable into an array saying that the delimiter is the "/" from there everything that is between "/" positions in the array, so you can manipulate the date the way you want it to.

What you can also do is work with the object DateTime of PHP, there would be that way:

$newDate = DateTime::createFromFormat('d/m/Y', $var_dtfim);
$var_dtfim = $newDate->format('m/d/Y');

Browser other questions tagged php sql-server

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