PHP & SQL Server 7.0 Date Problem!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

jayeshdave
Forum Newbie
Posts: 9
Joined: Tue Dec 17, 2002 6:17 am

PHP & SQL Server 7.0 Date Problem!

Post by jayeshdave »

I'm having a problem formatting dates using PHP. Basically when I pull a date from SQL Server 7.0 it comes up in the format "28 Sep 2002 0:00", what I want is it to come up "28/09/02".

How do I do this. I've tried reading PHP.net and looking at various posts on this site but I just found code related to MySql which I'm not sure is relevant. I'm new to PHP, so please explain thoroughly.

Below is code for my page.

?>
<HTML>
<HEAD>
<TITLE>
Products in Stock
</TITLE>
</HEAD>

<?php

$query = "SELECT DATE_FORMAT(DateOut, '%W %M %Y') FROM AllocatedStock";

if (!($result = mssql_query($query)))
{
echo("Error 1");
exit();
}
?>

<body background="images/webbackground.gif">
<DIV align="center">
<CENTER>
<TABLE border="0" cellpadding="0" cellspacing="0" width="90%">
<?php

while(($row = mssql_fetch_array($result)))
{
?>
<TR align="center">
<TD width="10%">
<?php
echo trim($row["ProductCode"]);
?>
</TD>
<TD width="10%">
<?php
echo trim($row["UID"]);
?>
</TD>
<TD width="20%">
<?php
echo trim($row["Description"]);
?>
<br>
<br>
</TD>
<TD width="14%">
<?php
echo trim($row["Name"]);
?>
</TD>
<TD width="14%">
<?php
echo trim($row["Contact1"]);
?>
</TD>
<TD width="10%">
<?php
echo trim($row["SalesPerson"]);
?>
</TD>
<TD width="10%">
<?php
echo trim($row["DateOut"]);
?>
</TD>
<TD width="10%">
<?php
$tokusch = $row["ReleasedToClient"];
If ($toclient < 1)
{
echo "No";
}
Else
{
echo "Yes";
}
?>
</TD>
</TR>
<?php
}

?>
</TABLE>
</CENTER>
</DIV>

<p><br>
</p>
</body>
</HTML>


Can you please edit the code if possible so I can see.

Thanks.
evilcoder
Forum Contributor
Posts: 345
Joined: Tue Dec 17, 2002 5:37 am
Location: Sydney, Australia

Post by evilcoder »

try this:
$displaydate = "%d/%m/%y";

date_format(DateOut,'$displaydate')
jayeshdave
Forum Newbie
Posts: 9
Joined: Tue Dec 17, 2002 6:17 am

Post by jayeshdave »

Doesn't work, it says:

Fatal error: Call to undefined function: date_format() in C:\Inetpub\wwwroot\...

Please help.
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

Code: Select all

<?php
$displaydate = "%d/%m/%y"; 

echo strftime($DateOut,$displaydate);
?>
jayeshdave
Forum Newbie
Posts: 9
Joined: Tue Dec 17, 2002 6:17 am

Still not working

Post by jayeshdave »

To make things simpler, here is a simplified version of my code.

Please can someone look at this and edit it so I can output dates correctly.

<?php
require "functions.php";

if ((check_ip()))
{
header("Location:http://$HTTP_HOST/$DOCROOT/logout.php");
exit();
}
?>
<HTML>
<HEAD>
<TITLE>
Products In Stock Report
</TITLE>
</HEAD>

<?php
$query = "SELECT DateOut From AllocatedStock";

if (!($result = mssql_query($query)))
{
echo("Error 1");
exit();
}
?>

<body background="images/webbackground.gif">
<DIV align="center">
<CENTER>
<TABLE border="0" cellpadding="0" cellspacing="0" width="100%">
<?php

while(($row = mssql_fetch_array($result)))
{
?>
<TR align="left">
<TD width="10%">
<?php
echo trim($row["DateOut"]);
?>
</TD>
</TR>
<?php
}
?>
</TABLE>
</CENTER>
</DIV>

<p>
</p>
</body>
</HTML>

Thank you.
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

I think this should do the trick:-

Code: Select all

<?php
<?php 
require "functions.php"; 

if ((check_ip())) 
{ 
header("Location:http://$HTTP_HOST/$DOCROOT/logout.php"); 
exit(); 
} 
?> 
<HTML> 
<HEAD> 
<TITLE> 
Products In Stock Report 
</TITLE> 
</HEAD> 

<?php 
$query = "SELECT DateOut From AllocatedStock"; 

if (!($result = mssql_query($query))) 
{ 
echo("Error 1"); 
exit(); 
} 
?> 

<body background="images/webbackground.gif"> 
<DIV align="center"> 
<CENTER> 
<TABLE border="0" cellpadding="0" cellspacing="0" width="100%"> 
<?php 

while(($row = mssql_fetch_array($result))) 
{ 
?> 
<TR align="left"> 
<TD width="10%"> 
<?php 
echo date("d/m/y",trim($row["DateOut"])); 
?> 
</TD> 
</TR> 
<?php 
} 
?> 
</TABLE> 
</CENTER> 
</DIV> 

<p> 
</p> 
</body> 
</HTML> 
?>
jayeshdave
Forum Newbie
Posts: 9
Joined: Tue Dec 17, 2002 6:17 am

Still doesn't work

Post by jayeshdave »

Thanks so far Takuma, but it still doesn't work. But this is the closest so far.

I get a list of dates but they all say:
01/01/70
01/01/70
01/01/70
01/01/70
01/01/70
01/01/70
01/01/70
01/01/70
01/01/70
01/01/70
01/01/70
01/01/70
01/01/70
01/01/70
01/01/70
01/01/70
... until the end of the no. of lines in the table.

All I need now is them to say the proper dates but in this format.

Many thanks so far.
evilcoder
Forum Contributor
Posts: 345
Joined: Tue Dec 17, 2002 5:37 am
Location: Sydney, Australia

Post by evilcoder »

Tats happening because you are overwriting what your database it told to output as the date.

When the newscript was installed you would have told the database how to display that date... right? and by redefining it in the script, its going to cough out the server date at the time for all the fields. i had the same problem on my site.

Gimmie till tomorrow, its like midnight here in oz, i'll look at my code and tell you how to fix it!

:)
jayeshdave
Forum Newbie
Posts: 9
Joined: Tue Dec 17, 2002 6:17 am

Post by jayeshdave »

Thank you evilcoder, I will be waiting for your reply.

If anyone else has any help it would be much appreciated!
User avatar
gyardleydn
Forum Commoner
Posts: 27
Joined: Tue Dec 03, 2002 8:27 am

Roll your own solution

Post by gyardleydn »

Hopefully someone will find a solution that uses a library function, or better yet provides a modification to your SQL code. In the meantime here is a simple function

Now I'm the type of guy who keeps a spare set of clothes in my trunk, but you may feel comfortable removing some of the tests in the function. On the other end you could convert dateArray elements 0 and 2 to absolute integers and test the values to assure valid output.

Code: Select all

<?php
Function ConvertMyDate($oldFormat = "")
{
static $monthConvert = array (
'Jan' => '01', 
'Feb' => '02', 
'Mar' => '03', 
'Apr' => '04', 
'May' => '05', 
'Jun' => '06', 
'Jul' => '07', 
'Aug' => '08', 
'Sep' => '09', 
'Oct' => '10', 
'Nov' => '11', 
'Dec' => '12');

$dateArray = explode(' ', $oldFormat);

if (count ($dateArray) <> 4) // not three spaces
	$outputDate =  '13/12/01';
else
	{
	if (array_key_exists ($dateArray[1], $monthConvert) == FALSE) // Month does not exist
		$dateArray[1] = '12';
	else
		$dateArray[1] = $monthConvert[$dateArray[1]];

	if (strlen ($dateArray[0]) < 2) // Day single digit
		$dateArray[0] = '0' . $dateArray[0];
	if (strlen ($dateArray[2]) == 4) // Year four digits
		$dateArray[2] = substr ($dateArray[2], 2, 2);

	$outputDate = $dateArray[0] . '/' .  $dateArray[1] . '/' . $dateArray[2];
	}

return $outputDate;
}

echo ConvertMyDate() . '<BR>';
echo ConvertMyDate("28 Sep 2002 0:00") . '<BR>';
?>
Why is it you only see errors after hitting sumbit ? :evil:
jayeshdave
Forum Newbie
Posts: 9
Joined: Tue Dec 17, 2002 6:17 am

Still doesn't work!

Post by jayeshdave »

Hi thanks for your help so far, but this code still doesn't work.

I get the message:

13/12/01

Fatal error: Call to undefined function: array_key_exists() in C:\Inetpub\wwwroot\...
User avatar
gyardleydn
Forum Commoner
Posts: 27
Joined: Tue Dec 03, 2002 8:27 am

OK then

Post by gyardleydn »

What version of php are you using? Array_key_exists() exists from version 4.1.0. Previously from 4.0.6 it existed as key_exists().

You can get away with removing

Code: Select all

if (array_key_exists ($dateArray[1], $monthConvert) == FALSE) // Month does not exist
		$dateArray[1] = '12';
	else
		$dateArray[1] = $monthConvert[$dateArray[1]];
and just leave

Code: Select all

$dateArray[1] = $monthConvert[$dateArray[1]];
You only have a problem if there is bad input or the months are formated differently than I expected. You need to test data with all twelve months. Correct $monthConvert if required.
jayeshdave
Forum Newbie
Posts: 9
Joined: Tue Dec 17, 2002 6:17 am

Still doesn't work!!!!!!!!!!!!!!!!

Post by jayeshdave »

Hi, if I delete the that piece of code, the page loads up, but it only seems to convert 1 date only.

Here is what I get:

13/12/01
28/09/02
31 May 2002 0:00
31 May 2002 0:00
5 Jun 2002 0:00
12 Jun 2002 0:00
16 May 2002 0:00
21 May 2002 0:00
5 Jun 2002 0:00

And goes on until the end with the long format.

Please HELP!!!
jayeshdave
Forum Newbie
Posts: 9
Joined: Tue Dec 17, 2002 6:17 am

Post by jayeshdave »

I was using PHP 404pl1

But after I tried upgrading to 4.2.3 I get:

Notice: Undefined offset: 1 in C:\Inetpub\wwwroot\ ... on line 16

Notice: Undefined index: in C:\Inetpub\wwwroot\ ... on line 16

Notice: Undefined offset: 2 in C:\Inetpub\wwwroot\ ... on line 20

Notice: Undefined offset: 2 in C:\Inetpub\wwwroot\ ... on line 23
0//
28/09/02
31 May 2002 0:00
31 May 2002 0:00

Which seems a lot worse to me!
User avatar
gyardleydn
Forum Commoner
Posts: 27
Joined: Tue Dec 03, 2002 8:27 am

Post by gyardleydn »

These were example calls:

Code: Select all

echo ConvertMyDate() . '<BR>'; 
echo ConvertMyDate("28 Sep 2002 0:00") . '<BR>';
Delete these. They are there to show that the code works

Find where you use the date variable in your code and use it as an argument of the function. Thus:

echo trim($exampleDateVarible);

becomes

echo ConvertMyDate($exampleDateVarible);

Where exampleDateVarible is some varible in your code.

As for Undefined offset that means that php is tryong to access a nonexistant array element.

I ran my original code
Output:
13/12/01
28/09/02

These lines in my original code (first post) prevents those errors, checking that there are four array elements, if bad input is received.

Code: Select all

if (count ($dateArray) <> 4) // not three spaces 
   $outputDate =  '13/12/01'; 
else 
   { 
// Code Here etc... See complete code in  first post
   $outputDate = $dateArray[0] . '/' .  $dateArray[1] . '/' . $dateArray[2]; 
   }
You have either modified the function I gave you, copied it incorrectly, or (I don't understand why it would) your php settings are causing the code to be interpreted differently. Look over my code and see if there are any difference to yours. If you haven't changed or miscopied it, try

Code: Select all

if (count ($dateArray) <> 4) // not three spaces 
   {
   $outputDate =  '13/12/01';  
   }
else 
   {

Unless someone else is finding this dicussion usefull I suggest any further communication between us on this issue occur using PM(Private Messaging). I can't see this discussion being helpful to others anymore.
Post Reply