PHP & SQL Server 7.0 Date Problem!
Moderator: General Moderators
-
jayeshdave
- Forum Newbie
- Posts: 9
- Joined: Tue Dec 17, 2002 6:17 am
PHP & SQL Server 7.0 Date Problem!
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.
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.
-
jayeshdave
- Forum Newbie
- Posts: 9
- Joined: Tue Dec 17, 2002 6:17 am
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
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.
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.
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
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.
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.
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!

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
- gyardleydn
- Forum Commoner
- Posts: 27
- Joined: Tue Dec 03, 2002 8:27 am
Roll your own solution
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.
Why is it you only see errors after hitting sumbit ? 
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>';
?>-
jayeshdave
- Forum Newbie
- Posts: 9
- Joined: Tue Dec 17, 2002 6:17 am
Still doesn't work!
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\...
I get the message:
13/12/01
Fatal error: Call to undefined function: array_key_exists() in C:\Inetpub\wwwroot\...
- gyardleydn
- Forum Commoner
- Posts: 27
- Joined: Tue Dec 03, 2002 8:27 am
OK then
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
and just leave
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.
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]];Code: Select all
$dateArray[1] = $monthConvert[$dateArray[1]];-
jayeshdave
- Forum Newbie
- Posts: 9
- Joined: Tue Dec 17, 2002 6:17 am
Still doesn't work!!!!!!!!!!!!!!!!
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!!!
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
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!
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!
- gyardleydn
- Forum Commoner
- Posts: 27
- Joined: Tue Dec 03, 2002 8:27 am
These were example calls:
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.
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
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.
Code: Select all
echo ConvertMyDate() . '<BR>';
echo ConvertMyDate("28 Sep 2002 0:00") . '<BR>';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];
}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.