0

I'm connecting to a mssql server database using my linux cpanel server with php & freeTDS, I can connect and pull all the data i need from the DB but when I pull a date from the database its comes through as a different format,

here is my code

    $db = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer");

    $query = "SELECT * FROM Invoices Where ID = '".$id."'"; 
                     $result = mssql_query($query);

                    $invoice_d = mssql_result($result,$i,'invoice_date');

    echo $invoice_d;

But this returns as "Jun 16 2017 12:00:00:AM"

In the mssql databse it is saved in the format 2017-06-16, so i'm not sure why its being or where its being converted.

I have tried converting this unsing date_format and strtotime but none will work, i have also edited the local.conf file in the freetds folder but to no avail.

Thanks

Simon Gersh
  • 41
  • 5
  • Is the date column actually stored with a `DATE` datatype in the database? –  Jun 18 '17 at 23:27
  • Also, did you know the mssql_ library of functions was removed in php7? If thus is new development, you should switch over to the sqlsrv_ library before it gets too big. Aldo, with sqlsrv_, when you select a `DATE` column, you get back a php [DateTime](http://php.net/manual/en/datetime.format.php) object. From there, you can get any format you want. –  Jun 18 '17 at 23:34
  • Hi Terminus, I can confirm it is a date feild in the ms sql database, I looked at php 7 and sqlsrv_ but was told it wouldnt work on my linux machine. – Simon Gersh Jun 19 '17 at 10:28
  • Earlier versions of SQLSRV were Windows-only. I believe current versions for PHP/7 finally work on Linux though I have no idea about their stability. – Álvaro González Jun 19 '17 at 10:38
  • Do results change if you run this query before yours? `SET DATEFORMAT ymd`? – Álvaro González Jun 19 '17 at 10:39
  • Looks like there's an [old comment on the doc page for mssql_query](http://php.net/manual/en/function.mssql-query.php#96230) with a possible answer. Basically, set `mssql.datetimeconvert = Off` in your php.ini. –  Jun 19 '17 at 11:31

0 Answers0