Link to home
Create AccountLog in
Avatar of Overthere
Overthere

asked on

PHP and extracting date columns from MS SQL Server

I am trying to extract a  date from a column in a MS SQL table that is just defined as "date".
The column does have a date - 2016-02-24
What's the magic trick??
If comment out my line to extract the date, processing continues extracting the rest of the data from the row and displaying it exactly as it should.  
If I uncomment the line to allow it to execute to extract and display the date,  it stops and I have
no error in my console, nor anywhere else...I am doomed...

                           <tr>      
                                <td><label class="labeldisplay"><?php $regbegdate = $row['RegBegDate'];
					                                  	 echo "Beg Date:  " . $regbegdate . "<BR><BR>";
                                                        $regenddate = $row['RegEndDate'];
						                                 echo "End Date:  " . $regenddate . "<BR><BR>"
						                               	 echo "Travel Beg Date:</label><span style=background-color:yellow><label class='labeldata'>" .
														 $regbegdate . " TO " . $regbegdate;
								          			      ?></label></span></td>			   
 							<tr>

Open in new window

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Suggest you add error_reporting(E_ALL) and ini_set('display_errors', TRUE) to your scripts.  You really want to see the errors when you're trying to debug the scripts!

You may also want to use var_dump($row) before you start using any of the contents of $row.  This will let you see what might be in the array, or whether $row is, in fact, an array.  It might not be.  A query failure might have returned FALSE in $row instead of an array of query results.

If you're new to PHP and want some good learning resources, this article may be helpful:
https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html
Avatar of Dave Baldwin
There is no magic trick and your code above looks fine.  Except this line does not have a ';' at the end.
echo "End Date:  " . $regenddate . "<BR><BR>";

Open in new window

+1, Dave.  If your display_errors() setting is correct, you will likely see a Parse Error message on that line!
Avatar of Overthere
Overthere

ASKER

the leaving out was a pasting error... darn mouse...
I do have :

error_reporting(E_ERROR | E_PARSE | E_NOTICE);

Open in new window


and that is what is confusing me... not showing anything...
I will try the var_dump...
I tried the var_dump - I keep forgetting about that handy command - and it shows that the row has data and populates the var accordingly. I have attached a screen shot of it.
It dies on the echo statement.
I do receive a HTML error, that I believe is a result of the echo statement not executing since it contains the  opening tags for the label and span.
If I leave out the date section, the script continues to execute successfully and displays the data correctly.
I have attached the coding lines before/after my date section. Maybe a fresh pair of eyes can see the difference.
Even if I just try to echo the date out, it fails.

							<td><label class="labeldisplay"><?php $birth = $row['CertifiedBirthCert'];
							                                $result = get_chkbox($birth); 
							                                echo "State Certified Birth Certificate Req:</label><span style=background-color:yellow><label class='labeldata'>" .
							                                $result; ?></label></span></td>
						</tr>
                        <tr>      
                            <td><label class="labeldisplay"><?php 
								                            var_dump($row['RegBegDate']);
								                            $regbegdate = $row['RegBegDate'];
														    var_dump($regbegdate);
					                                        echo "Beginning Date:  " . $regbegdate .  "<BR><BR>";														
														    var_dump($row['RegEndDate']);
                                                            $regenddate = $row['RegEndDate'];
						                                    echo "End Date:  " . $regenddate . "<BR><BR>";
						           echo "Beg Date:</label><span style=background-color:yellow><label class='labeldata'>"  .  $regbegdate . " TO " . $regbegdate;
                                                             ?></label></span></td>	
								          					   
 						</tr>
							<td><label class="labeldisplay"><?php 										
															echo "Final Destination:</label><span style=background-color:yellow><label class='labeldata'>" .
															$row['FinalDestination'];?></label></span></td>
							</tr>
							<tr><td></td></tr>
							<tr><td></td></tr>
						</table>								

Open in new window

Okay, here's what worked but I don't like it one bit cause there will be times when I can not alter the sql select statement.
So, I need to find a way to convert the date after I assign or as I assign it to a var.
Any suggestions on that ???
Here's the sql statement and now it displays correctly..

select *, LEFT(CONVERT(nvarchar,BldInfo.RegBegDate,120), 10) as RegBegDate, 
  LEFT(CONVERT(nvarchar,BldInfo.RegEndDate,120), 10) as RegEndDate
  from BldInfo where BldingId = 'P09'
]

Open in new window

SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
WOW - thank you everyone...
I like the idea of stuffing a heredoc - didn't think of that.
I have tried various methods because when I just use:

$trvbegdate = date("m-d-Y", strtotime($row['RegBegDate']));

Open in new window


it does  not translate the date correctly i.e 2012-02-18 becomes 12-31-1969
SO I decide to use this: but... it fails giving the error:Call to a member function format() on a non-object

$dateDE =  $row['RegBegDate'];
$dateObj = DateTime::createFromFormat("Y-m-d", $dateDE);
$trvlbegdate = $dateObj->format("m/d/Y");
$trvlbegdate = date('m/d/Y',strtotime($trvlbegdate));

Open in new window

Please, please, please show us that var_dump($row) now!  Once we see the data we have a much better chance of helping, and not wasting your time!
'strtotime' is actually pretty picky about what it converts.  I'd be interested in seeing the actual contents of $row['RegBegDate'].  Microsoft SQL has about 5 different date formats and I don't 'strtotime' will handle all of them.
I don't mean to forget to attach things, but here is the dump of the data.
I thought I had attached it an earlier posting.
I can retrieve the format I want with a Sql statement, but there are going to be times when I can not do so.
That being said, the coding I posted before has always worked, but not this time.
I would really like it to be  d-M-Y i.e. 02 -FEB-2012 etc
Anyway, see attached screen shot.
C--Users-marthaj-Desktop-Dana-Folder.PNG
This appears to be a picture of a dump of some of the data but not the $row variable.  It does not really look like the output of var_dump($row). Any chance we can get what we asked for?
Microsoft SQL has about 5 different date formats and I don't 'strtotime' will handle all of them.
You can specify how the date comes out in the query. You output it in a format strtotime understands and use date() to get the desired result.

But agree with Ray here - we are not getting what we asked for.

A data dump (at least the one I was interested in) is of your database - not your application. Can we see what the raw data looks like in the database as well as the var_dump($row).
No, Microsoft SQL has 5 different 'date' data types.  Really.  Oh, sorry... there are 6.  https://msdn.microsoft.com/en-us/library/ff848733.aspx
@Dave - misunderstanding - I was referring to this
http://www.w3schools.com/sql/func_convert.asp
Converting date to a different representation.

So something like
SELECT convert(varchar, RegBegDate, 120) from table

Open in new window


Result
2016-02-02 14:04:03

Open in new window


$date = date('YMD', strtotime($date);

Open in new window


Just to ensure that the date comes out in a format that strtotime understands.
Julian, I think Overthere will probably have to use that function also.  In another question, it turned out that SQL Server did Not return a string version of the date unless you did an explicit conversion.
The screen shot is exactly what appeared when I did executed a var_dump of the row in my coding.
Below is the line of coding that generated it:

var_dump($row['RegBegDate']);

Open in new window


And I have once again, attached the screen shot...
Is that not correct??
Do you want just what an actual row of data looks like in the table??
I know client is not going to agree to posting live data here.
But I can post a snippet of it - copied and pasted from table:
    RegBegDate	           	      RegEndDate
2012-02-18 00:00:00.000	2012-02-24 00:00:00.000

Open in new window



I can generate what I want using a Sql query but I know there is two places where that is not feasible or workable.
Maybe the only way I can achieve the desired results is by the Sql query - that is what I am now trying to find out.
I don't mean to be so frustrating to you folks, its just that is what I am getting.
C--Users-marthaj-Desktop-Dana-Folder.PNG
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Okay David, I will try it and see what happens. I wonder why it got cut off in the screen shot??
Better things to wonder about huh?
I want to say to everyone, I apologize if I was frustrating you all by not posting what you wanted.
The truth is I thought I was and apparently I wasn't...
Better things to wonder about huh?
Because your var_dump was inside a container that clipped the results.

Rather than a screen shot - in this case - view source find the dump in the HTML source and copy it from there as text. That way you get all of it in text format - which we can use if necessary
But I a confused

The screen shot shows this
public 'date' string => '2012-02-18 00:00:00' (length = 19)

Open in new window

The length cannot be 19 if there is a .000 on the end ...
I don't think that the image and the text are showing the same thing.  That's another reason for showing the actual 'var_dump' instead of the image.
After doing some research, it appears that you have SQL Server 2008 or newer and you are using a 'datetime2' data type for that column.  A plain 'datetime' data type would not have the '.000' at the end and would probably work.  Can you change it?
The second post might have been a dump from the Database itself - the way it is laid out suggests it was not done with PHP.
@Overthere: This is a good thing to take in: SSCCE

The reason we ask for specific things, like the output of var_dump() or error_reporting(E_ALL) or the browser "view source" is because we have learned through experience that descriptions of output or pictures of output can obscure important meanings and nuances in the underlying data.  How long is a string of data?  If you look at it in a browser, you cannot tell because browsers collapse whitespace, and CSS can show or hide parts of the data.  Does it contain any "illegal characters?"  You can't know because browsers may hide these.  This really should not be that hard to get right - we just have to use the correct tools to examine the data, then choose the correct PHP functions to manipulate the data.

This seems to show some of what we're dealing with.  I find strtotime() to be OK with these date representations.  PHP 5.4.45.

You can install this code on your server and run it to see if you get the same output.
http://iconoun.com/demo/temp_overthere.php
<?php // demo/temp_overthere.php
/**
 * http://www.experts-exchange.com/questions/28922954/PHP-and-extracting-date-columns-from-MS-SQL-Server.html#a41448385
 *
 * A good thing to know (make a Google search) "SSCCE"
 */
error_reporting(E_ALL);

// STRLEN:     1...5...10...15...20...25 LOOKS LIKE IT IS 23, NOT 19
//                               |   |
//                               V   V
$ReqBegDate = '2012-02-18 00:00:00.000';
$reqEndDate = '2012-02-24 00:00:00.000';

$ts = strtotime($ReqBegDate);
var_dump($ts);
$is = date('c', $ts);
var_dump($is);

Open in new window

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you David, Ray and Julian. I split points and if that is not suitable or if the amount is not suitable, please
let me know, If I could have, I would have given each of you 500 points because it all helped.
I will remember to post the "source" and not a screen shot etc in the future and be more mindful
of you suggestions.  thank you...:)
You're welcome.  Glad to help.