Date conversion in MSSQL query not working when displayed via PHP

Question

I have a PDO statement, thus. The data is coming from a SQL Server database:

//Get full record from form108 database
$data = $con->prepare("SELECT CONVERT(varchar, p.WorkBeginDate, 101), * FROM Employee AS e 
INNER JOIN EmployeeProjectInfo AS p ON e.empUIN = p.empUIN WHERE e.empUIN = :empuin AND   
p.UID = :uid");
$input = array('empuin'=>$_SESSION['UIN'],'uid'=>$_SESSION['ID']);
$data->execute($input);

Everything displays fine except the date. Instead of the desired format, mm/dd/yyyy, I get yyyy-mm-dd. My hope was that the CONVERT() function would work like the DATE_FORMAT() function in MySQL. The data type in the database is date. What am I doing wrong?

date is stored as yyyy-mm-dd. Why do you assume that the application or the database know that your desired format is mm/dd/yyyy? — STT LCU
– STT LCU, Commented Dec 12, 2013 at 15:05

Carlos · Accepted Answer · 2013-12-12 15:07:58Z

1

Theres nothing wrong... all you have to do is change it to the previous format. Here's a very basic example:

$mydate = $result['dateField'];
echo date('m/d/Y', strtotime($mydate)); //prints: 12/25/2013

answered Dec 12, 2013 at 15:07

Carlos

2383 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Over a year ago

Thanks, that did work. Is there no way to do it within the query? In MySQL, I've used the DATE_FORMAT function with success. I guess it doesn't work the same way in MSSQL?

Carlos Over a year ago

I dont know much about mssql but ive found something useful: mysqltomssql.blogspot.com.br/2011/04/dateformat-syntax.html and sql-server-helper.com/tips/date-formats.aspx ... hope it help.

Collectives™ on Stack Overflow

Date conversion in MSSQL query not working when displayed via PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related