mavmanau
asked on
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,RiskLev elID 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!
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,RiskLev
//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!
SQL has ways of formatting a DATETIME field so that all you get is a DATE and not a time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
following that process this should work then yes?
SELECT BrandID,CONVERT(VARCHAR(10 ),DateFund ed,111),Ri skLevelID FROM DB Where CONVERT(VARCHAR(10),DateFu nded,111) is not NULL and CONVERT(VARCHAR(10),DateFu nded,111) between '2011-09-01' and '2011-09-30' order by CONVERT(VARCHAR(10),DateFu nded,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?
following that process this should work then yes?
SELECT BrandID,CONVERT(VARCHAR(10
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?
ASKER
Hi,
Worked it out. the format of the data had slashes rather than dashes. Thank you for your help!
Worked it out. the format of the data had slashes rather than dashes. Thank you for your help!
ASKER
Thank you for your help!
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.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html