Solved

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

Posted on 2011-09-30
6
470 Views
Last Modified: 2013-12-12
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!

0
Comment
Question by:mavmanau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36895260
SQL has ways of formatting a DATETIME field so that all you get is a DATE and not a time.
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 36895272
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
 

Author Comment

by:mavmanau
ID: 36895759
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:mavmanau
ID: 36895906
Hi,

Worked it out.  the format of the data had slashes rather than dashes.  Thank you for your help!
0
 

Author Closing Comment

by:mavmanau
ID: 36895926
Thank you for your help!
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36896506
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

Featured Post

WordPress Tutorial 4: Recommended Plugins

Now that you have WordPress installed, understand the interface, and know how to install new parts, let’s take a look at our recommended plugins.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question