2

I have googled extensively, seen various stackoverflow solutions, but none work here. Not sure why. Help would be great!

The date is stored in a smalldatetime field in MSSQL. The value of this is seen as "2015-11-27 00:00:00". This seems the same format as a date in mySQL. The example below works if the value is pasted in, but not the value from the recordset.

    //gives correct result, 11/27/2015
    $TravDate = date("m/d/Y", strtotime("2015-11-27 00:00:00"));

    //gives wrong result, 12/31/1969
    $TravDate = date("m/d/Y", strtotime($obj->TravDate));
Paul
  • 117
  • 2
  • 9
  • 1
    print_r on $obj->TravDate ? – Buddhi741 Aug 28 '15 at 19:56
  • 1
    and try to echo its type and submit your answer here for us to see – Buddhi741 Aug 28 '15 at 19:58
  • do the following and edit into you question: var_dump($obj->TravDate); – dlporter98 Aug 28 '15 at 20:04
  • Sorry, first time use print_r and not sure how to echo the type. Tried below and nothing appeared, as also straight echo of $obj->TravDate echo '
    ';
        print_r ($obj->TravDate);
        echo '
    ';     – Paul Aug 28 '15 at 20:10
  • That's probably your problem, then. TravDate is empty... – rjdown Aug 28 '15 at 20:12
  • Tried echo "
    TravDateDump=".var_dump($obj->TravDate); and this gave no value for the field. All other non-date database fields gave values. In Studio Express the value can be seen as 2015-11-27 00:00:00. So problem is definitely extracting this value into PHP.     – Paul Aug 28 '15 at 20:34

3 Answers3

2

Please try

  $datetime = new DateTime( $obj->travdate); //create datetime object with received data
    $reform = $datetime->format('Y M d');   //do reformat as required

Refer to http://af-design.com/blog/2010/03/13/microsoft-sql-server-driver-for-php-returns-datetime-object/

Subin Thomas
  • 1,408
  • 10
  • 19
  • That is msql. Am using sqlsrv for MSSQL. – Paul Aug 28 '15 at 20:36
  • 1
    Thanks that worked! Did not expect anything for reason you gave, but to my surprise it gave "reform=2015 Aug 28" which is correct. – Paul Aug 28 '15 at 21:13
  • Sorry, update. That gave today's date, not 11/27/2015, so seems there is a problem with getting a value here. – Paul Aug 28 '15 at 21:22
  • It acts like it is blank. But all other values gave correct values using $obj->xxx, and in Studio Express I see the value "2015-11-27 00:00:00". I have just run the same recordset in ASP and it gave the correct value. So why is $obj -->travdate null if $obj --> is working for other fields and there IS a non-null value in the database? – Paul Aug 28 '15 at 21:35
  • My answer is updated with a link. Check there, return date time as string, configuration. – Subin Thomas Aug 28 '15 at 21:58
  • This link looks like it explains why there is a problem, and provides solutions. I have not tried, as I used the solution below (converting the field to a string in the SQL query), but assuming these solutions work I will mark as solution. Thanks! – Paul Aug 29 '15 at 00:40
0

Be sure the $obj->TravDate serialized to string is correct, probably solved value is not correct. Try firstly stringify and after use strtotime() function.

PC-CD Interactive Multimedia
  • 46
  • 2
  • 1
    Sorry, newbie here. Field values were taken from database, not serialized from a form. Tried the following code, gave error:- 'code' $TravDate=$obj->TravDate; $TravDate=(string)$TravDate; $TravDate = date("m/d/Y", strtotime($TravDate)); – Paul Aug 28 '15 at 20:28
0

Solution found. I gave up using PHP method above: it worked for all other variables in the recordset except the date field TravDate, which is not null.

Solution involved changing the SQL query by converting the date field as follows:- CONVERT(varchar(10), TravDate, 20) AS TravDate (php echo giving 11/27/2015). This solution found in stackoverflow.

Community
  • 1
  • 1
Paul
  • 117
  • 2
  • 9