PHP Input MS SQL Failed to convert date and/or time from character string

Question

I'm running a MSSQL function and returning to PHP and I'm using the function twice in the PHP and writing it away to a MySQL database.

The first time I run the function I'm using $to and $from (I'm well aware it's not pretty, I'm not a php coder by trade...)

$to = date("j M Y", mktime());
$year = date("Y", mktime());
$jan = '1 jan ';
$from = $jan . $year;

I'm then using the following

select * from MISYearToDate('$from', '$to')

and that works absolutely fine.

When I then do the same thing but putting in this week's dates using

$weekstarting = date('j M Y', strtotime('last sunday'))."<br>";
$weekending = date('j M Y', strtotime('next saturday'))."<br>";

and then run

select * from MISYearToDate('$weekstarting', '$weekending')

I'm getting the error

[Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from character string. )

$From and $to echo

1 jan 2012
27 Jun 2012

$weekstarting and $weekending echo

24 Jun 2012
30 Jun 2012

So as far as I can tell, there really isn't a difference and I'm pretty damn confused!

Marc B · Accepted Answer · 2012-06-27 11:22:09Z

1

$weekending = date('j M Y', strtotime('next saturday'))."<br>";
                                                       ^^^^^^^

<br> is not a valid component of an SQL date string. You're probably viewing the output in a browser, so the <br> is effectively hidden, but that's where your problem is.

That's turning your query into:

select * from MISYearToDate('24 Jun 2012<br>', '30 Jun 2012<br>')

answered Jun 27, 2012 at 11:22

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

2 Comments

franglais Over a year ago

Sometimes it's the simplest things... That will teach me to try to combine 2 tasks in one line of code :D Thank you!

Marc B Over a year ago

No problem. I'd suggest always doing a 'view source' anytime you're debugging PHP output in a browser, especially if you're mixing html with your data - the browser will essentially lie to you, because it doesn't know you'e debugging.

Nigel Coates · Accepted Answer · 2012-06-27 11:13:04Z

1

I'd recommend only feeding dates into SQL in the format of yyyy-mm-dd. This will ensure SQL doesn't get confused with the date format.

SQL will not accept dates in the format of 1 jan 2012 (as a string).

answered Jun 27, 2012 at 11:13

Nigel Coates

462 bronze badges

2 Comments

franglais Over a year ago

So, just for my knowledge, how come it accepted the $from and $to strings?

franglais Over a year ago

Also, I'm no longer getting the message, however I am getting very different results returning in SSMS and PHP... e.g. in PHP I'm getting "Bob Nudd 0 0 0 0" whereas in SSMS I'm getting "Bob Nudd 1 5 23 15"

Collectives™ on Stack Overflow

PHP Input MS SQL Failed to convert date and/or time from character string

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related