PHP/MSSQL DateTime field Not Pulling Correctly

  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

I'm using a PHP script on a MSSQL server. Simply, I have a table that has a datetime field which looks like it gets stored for example '2011-06-30 12:20:58.360' but when I extract that column and try to run it in another query I get 'Jun 30 2011 12:20PM' automatically. I do no formatting.

Code: [ Select ]
//.. select and loop with mssql_fetch_array()

$discardeddate = $row['DiscardedDate'];
$discardeddate_ts = strtotime($discardeddate);

$dh = date('Y-m-d H:i:u', $discardeddate_ts);

$table2 = "SELECT * FROM table2 WHERE discardeddate = '$discardeddate'";
// this doesn't work because my query has discardeddate = 'Jun 30 2011 12:20PM' instead of it needs to match '2011-06-30 12:20:58.360'                
  1. //.. select and loop with mssql_fetch_array()
  2. $discardeddate = $row['DiscardedDate'];
  3. $discardeddate_ts = strtotime($discardeddate);
  4. $dh = date('Y-m-d H:i:u', $discardeddate_ts);
  5. $table2 = "SELECT * FROM table2 WHERE discardeddate = '$discardeddate'";
  6. // this doesn't work because my query has discardeddate = 'Jun 30 2011 12:20PM' instead of it needs to match '2011-06-30 12:20:58.360'                


It seems when I strtotime I lose the 58 seconds cause I get '2011-06-30 12:20:000000'. So, I think the Jun 30 2011 12:20PM is getting strtotime. I'm hoping someone can narrow down the solution cause I think it's going to be a server/os/ or php.ini setting. But maybe it will be something simple with the query or code. Thanks.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

I've added mssql.datetimeconvert = Off to my php.ini.

I now atleast get the seconds. So, I get '2011-06-30 12:20:58' but this still won't match to table2 '2011-06-30 12:20:58.360'. Is there a way to change the default PHP format when turning off datetimeconvert?
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

Scratch that. I removed the mssql.datetimeconver = Off and just left it back to default. Mine was neither on or off.

I then was able to fix this by using the CONVERT command inside the query. The 121 is the style code. Search CAST and CONVERT for MS SQL.

Code: [ Select ]

$getright_date = "SELECT CONVERT(varchar(23),DiscardedDate,121) as ritedate FROM table1 WHERE id = '1'";
$rungetright_date = mssql_query($getright_date);
while ($row = mssql_fetch_array($rungetright_date)) {
    $actualstored_date = $row['ritedate'];
}

// now I can use $actualstored_date to make comparisons in other MSSQL tables

$table2 = "SELECT * FROM table2 WHERE discardeddate = '$actualstored_date'";
  1. $getright_date = "SELECT CONVERT(varchar(23),DiscardedDate,121) as ritedate FROM table1 WHERE id = '1'";
  2. $rungetright_date = mssql_query($getright_date);
  3. while ($row = mssql_fetch_array($rungetright_date)) {
  4.     $actualstored_date = $row['ritedate'];
  5. }
  6. // now I can use $actualstored_date to make comparisons in other MSSQL tables
  7. $table2 = "SELECT * FROM table2 WHERE discardeddate = '$actualstored_date'";


So, I've got to make an extra call and let the SQL handle returning the needed date format to php. Additionally, I could not get CONVERT(datetime,DiscardedDate,121) to work as I guess because the field is already a datetime.

Hope this helps someone. I can't believe a database wants to store one value, but return another. I think the conversions should be left up to the script and not the sql. Mainly because with script I can display the date any way I need to as I may need it displayed several different ways. Overall, I guess this is a view setup in the MSSQL server which could be configured the same for other DBs and not necessarily MS's fault, but I'd have to say this problem along with the fact that there's no LIMIT SQL is really peeving me off at MSSQL server.

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: No registered users and 95 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.