How to format a date in the MS SQL query using PHP and PDO

Hello Experts!

Firstly thank you for taking a look at my question.

Essentially I have a query which one of the fields is a date field where it contains the time like below.  I want to only pull out the date format like this: 2011-09-01 and do a group by on this field as well.

What it currently comes out like: 2011-09-01 12:38:06.413

I can do a date format on it when I am printing it to the screen and to a csv file, but to do the group by I need it to get rid of the time.

This is my query I am currently using which works, but wanted to add the group by to it.

$query = "SELECT BrandID,DateFunded,RiskLevelID FROM DB Where DateFunded is not NULL and DateFunded between '".$firstmonth."' and '".$today."' order by DateFunded" ;
//Load the query
$stmt = $c->prepare($query) ;
 //Run the query
 $stmt->execute();

Also how do I add some trouble shooting to the end of the query so I can see any errors that pop up?
Thank you, your assistance is appreciated!

mavmanauNetwork Engineer/SysadminAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ray PaseurConnect With a Mentor Commented:
This might not be my final choice, but SELECT CONVERT might be perfect for your needs.  
http://blog.sqlauthority.com/2007/06/10/sql-server-retrieve-select-only-date-part-from-datetime-best-practice/
0
 
Ray PaseurCommented:
SQL has ways of formatting a DATETIME field so that all you get is a DATE and not a time.
0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
Hi,

following that process this should work then yes?  

SELECT BrandID,CONVERT(VARCHAR(10),DateFunded,111),RiskLevelID FROM DB Where CONVERT(VARCHAR(10),DateFunded,111) is not NULL and CONVERT(VARCHAR(10),DateFunded,111) between '2011-09-01' and '2011-09-30' order by CONVERT(VARCHAR(10),DateFunded,111)

I must have missed something however, because it returns nothing out of the DB, and if I remove all the converts etc, it works perfectly.

Can you see anything blatently obvious that I have missed?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mavmanauNetwork Engineer/SysadminAuthor Commented:
Hi,

Worked it out.  the format of the data had slashes rather than dashes.  Thank you for your help!
0
 
mavmanauNetwork Engineer/SysadminAuthor Commented:
Thank you for your help!
0
 
Ray PaseurCommented:
Aha!  Slashes have some different meanings to date conversion.  You might like the article here.  It has a lot about mySQL (not MSSQL) but most of the principles are equally applicable.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
All Courses

From novice to tech pro — start learning today.