SQL query in PHP and date format

Question

I have a PHP function that interacts with my SQL Server. It looks like this:

public function buscaRelatorioHospedagem($nome,$de,$ate){ // TODO Essa função pode ser aproveitada pra aba nova
    $de--;
    $de++;
    $ate--;
    $ate++;

    $sqlrelatorio = "SELECT [NomeHospede] as nome,COUNT(NomeHospede) as cont_reservas
    FROM [vpg_intercity].[dbo].[FT_HospedagemInvestidores]";
    $sqlrelatorio .= " WHERE NomeHospede LIKE '%$nome%'";
    $sqlrelatorio .= " AND DataPartida >= '$de' AND DataChegada <= '$ate'";
    $sqlrelatorio .= " GROUP BY NomeHospede";
    $res = System::element('mssqldb')->select($sqlrelatorio);
    $retorno =  array();
    foreach ($res as $r){
        $obj = array();
        $obj['nome'] = $r['nome'];
        $obj['cont_reservas'] = $r['cont_reservas'];
        $retorno[] = $obj;
    }
    return($retorno);
}

It should bring a name and a total number of reservations. The search by name works well, but the dates do not yet. As it is a site that I took over from another developer, I don’t know how is the format of the dates of the variables $de and $ate. I want to make sure it is always in YYYY-mm-dd format (2015-05-25, for example).

What can I do?

Answer 1

I found the solution, is the following (based on another page of the site, made by the previous developer of the same):

Dates $de and $ate are transformed as follows, to have separate day, month and year:

$deAux = explode("/",$de);
$ateAux = explode("/",$ate);

$diaDe  = $deAux[0];
$mesDe  = $deAux[1];
$anoDe  = $deAux[2];

$diaAte = $ateAux[0];
$mesAte = $ateAux[1];
$anoAte = $ateAux[2];

And then, my function was as follows (attention pro SQL in the parts of the date filter):

public function buscaRelatorioHospedagem($nome,$diaDe,$mesDe,$anoDe,$diaAte,$mesAte,$anoAte){ // TODO Essa função aproveitada pra aba nova
    $diaDe--;
    $diaDe++;
    $diaAte--;
    $diaAte++;

    $mesDe--;
    $mesDe++;
    $mesAte--;
    $mesAte++;

    $anoDe--;
    $anoDe++;
    $anoAte--;
    $anoAte++;

    $sqlrelatorio = "SELECT [NomeHospede] as nome,COUNT(NomeHospede) as cont_reservas
    FROM [vpg_intercity].[dbo].[FT_HospedagemInvestidores]";
    $sqlrelatorio .= " WHERE NomeHospede LIKE '%$nome%'";
    $sqlrelatorio .= " AND DataPartida >= cast($anoDe as varchar(4))  + '-' + cast($mesDe as varchar(2))  + '-' + cast($diaDe as varchar(2))";
    $sqlrelatorio .= " AND DataChegada <= cast($anoAte as varchar(4)) + '-' + cast($mesAte as varchar(2)) + '-' + cast($diaAte as varchar(2))";
    $sqlrelatorio .= " GROUP BY NomeHospede";
    $res = System::element('mssqldb')->select($sqlrelatorio);
    $retorno =  array();
    foreach ($res as $r){
        $obj = array();
        $obj['nome'] = $r['nome'];
        $obj['cont_reservas'] = $r['cont_reservas'];
        $retorno[] = $obj;
    }
    return($retorno);
}

Answer 2

$sqlrelatorio .= " AND Format_DATA(DataPartida,'%Y-%m-%d') >= '$de' AND Format_DATA(DataChegada,'%Y-%m-%d') <= '$ate'";

The search will always return in this YYYY-mm-dd format, using the function FORMAT_DATA()
try using this function:

IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[format_date]') 
    AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[format_date]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET CONCAT_NULL_YIELDS_NULL OFF
GO

CREATE function [dbo].[format_date] 
(@inputdate datetime ,@format varchar(500))
returns varchar(500)
as
begin
declare @year varchar(4)            --YYYY
declare @shortyear varchar(4)           --Yr
declare @quarter varchar(4)         --QQ
declare @month varchar(2)           --MM
declare @week varchar(2)            --WW
declare @day varchar(2)         --DD
declare @24hours varchar(2)         --24HH
declare @12hours varchar(2)         --HH
declare @minutes varchar(2)         --MI
declare @seconds varchar(2)         --SS
declare @milliseconds varchar(3)        --MS
declare @microseconds varchar(6)        --MCS
declare @nanoseconds varchar(9)         --NS
declare @dayname varchar(15)            --DAY
declare @monthname varchar(15)          --MONTH
declare @shortmonthname varchar(15)         --MON 
declare @AMPM    varchar(15)        --AMPM
declare @TZ  varchar(15)        --TZ
declare @UNIXPOSIX   varchar(15)        --UNIXPOSIX
                --UCASE
                --LCASE
declare @formatteddate varchar(500)

--Atribuir data e hora atuais para

if (@inputdate is NULL or @inputdate ='')
 begin
 set @inputdate = getdate()
end

if (@format is NULL or @format ='')
 begin
 set @format ='YYYY-MM-DD 12HH:MI:SS AMPM'
end

-- Definir todos os valores
set @year       = convert(varchar(4),year(@inputdate))
set @shortyear  = right(@year,2)
set @quarter    = convert(varchar(1),datepart(QQ,(@inputdate)))
set @month      = right('0'+convert(varchar(2),month(@inputdate)),2)
set @week       = right('0'+convert(varchar(2),datepart(ww,(@inputdate))),2)
set @day        = right('0'+convert(varchar(2),day(@inputdate)),2)
set @24hours    = right('0'+convert(varchar(2),datepart(hh,@inputdate)),2)
set @TZ     = convert(varchar(10),datename(TZ,convert(varchar(20),@inputdate)))
set @UNIXPOSIX  = convert(varchar(15),datediff(ss,convert(datetime,'01/01/1970 00:00:000'),@inputdate))

if datepart(hh,@inputdate) >12
  begin
   set @12hours      = right('0'+convert(varchar(2),datepart(hh,@inputdate)) -12,2)
  end
else
  begin
   set @12hours      = right('0'+convert(varchar(2),datepart(hh,@inputdate)) ,2)
 end

 if datepart(hh,@inputdate) >11 
  begin
   set @AMPM ='PM'
  end
else
 begin
 set @AMPM ='AM'
end

set @minutes      = right('0'+convert(varchar(2),datepart(n,@inputdate)),2)
set @seconds      = right('0'+convert(varchar(2),datepart(ss,@inputdate)),2)
set @milliseconds = convert(varchar(3),datepart(ms,@inputdate))
set @microseconds = convert(varchar(6),datepart(mcs,@inputdate))
set @nanoseconds  = convert(varchar(9),datepart(ns,@inputdate))
set @dayname      = datename(weekday,@inputdate)
set @monthname    = datename(mm,@inputdate)
set @shortmonthname= left(datename(mm,@inputdate),3)
set @formatteddate = @format
set @formatteddate=replace(@formatteddate,'MONTH',@monthname)
set @formatteddate=replace(@formatteddate,'MON',@shortmonthname)
set @formatteddate=replace(@formatteddate,'AMPM',@AMPM)

set @formatteddate=replace(@formatteddate,'YYYY',@year)
set @formatteddate=replace(@formatteddate,'Yr',@shortyear)
set @formatteddate=replace(@formatteddate,'QQ',@quarter)
set @formatteddate=replace(@formatteddate,'WW',@week)
set @formatteddate=replace(@formatteddate,'MM',@month)
set @formatteddate=replace(@formatteddate,'DD',@Day)
set @formatteddate=replace(@formatteddate,'24HH',@24hours)
set @formatteddate=replace(@formatteddate,'12HH',@12hours)
set @formatteddate=replace(@formatteddate,'Mi',@minutes)
set @formatteddate=replace(@formatteddate,'SS',@seconds)
set @formatteddate=replace(@formatteddate,'MS',@milliseconds)
set @formatteddate=replace(@formatteddate,'MCS',@microseconds)
set @formatteddate=replace(@formatteddate,'NS',@nanoseconds)
set @formatteddate=replace(@formatteddate,'DAY',@dayname)
set @formatteddate=replace(@formatteddate,'TZ',@TZ)
set @formatteddate=replace(@formatteddate,'UNIXPOSIX',@UNIXPOSIX)

if charindex('ucase',@formatteddate)<>0
 begin
  set @formatteddate=replace(@formatteddate,'ucase','')
  set @formatteddate=upper(@formatteddate)
 end

if charindex('lcase',@formatteddate)<>0
begin
 set @formatteddate=replace(@formatteddate,'lcase','')
 set @formatteddate=lower(@formatteddate)
end

return @formatteddate
end

GO

to use:
select dbo.format_date(GETDATE(),'YYYY-MM-DD') as Date

FORMAT_DATA()

Answer 3

You can use the Convert

DataPartida >= convert(datetime, '$de', 120)

SOURCE: http://www.w3schools.com/sql/func_convert.asp