1

I get this error message:

The conversion of a nvarchar data type to a datetime

when I try to bind a MySQL datetime variable to a smalldatetime variable in SQL Server statement. The code looks like:

$s = $dbcnx->prepare("SELECT MAX(attr_81577_) AS max_date FROM object_70969_");
$s->execute();
$r = $s->fetch(PDO::FETCH_ASSOC);
$max_date = $r[max_date]; // we got the maximum date from MySQL table

$s = $dbms->prepare("SELECT * FROM orgs WHERE update_date >= :max_date"); // Now we want to filter SQL Server table based on $max_date value
$s->bindParam(':max_date', $max_date);
$s->execute();

What is wrong with that and what can I do?

Jacobian
  • 10,122
  • 29
  • 128
  • 221
  • What's the *rest* of the error message? (It's likely to be something like "...resulted in an out-of-range value", for example. You're definitely missing the end of that sentence.) And what's the actual value of `$max_date`? Bear in mind that MySQL's `datetime` has a possible range of 1 January 1000 to 31 December 9999, whereas SQL Server's `smalldatetime` runs from January 1, 1900, through June 6, 2079, so it's quite possible you're trying to use an out-of-range date value. (Does it work as `SELECT * FROM orgs WHERE CAST(update_date AS datetime) >= :max_date`?) – Matt Gibson Sep 18 '15 at 19:40
  • You are right, The error message ends with `"...resulted in an out-of-range value"`. It is interesting, but if I print maximum values from MySQL table and SQL Server table, then they have exactly the same format like` 2015-09-18 16:22:00` – Jacobian Sep 18 '15 at 19:45
  • You didn't quote the column name alias there $max_date = $r["max_date"]; – Fevly Pallar Sep 18 '15 at 19:45
  • @Fevly Pallar. It makes no difference. – Jacobian Sep 18 '15 at 19:47
  • What, exactly, is in `$max_date` when you bind it to the parameter? Do a `var_dump($max_date)` and show us the result. Bear in mind that you're dealing with the string representation of dates here, not actual dates, so if the values are okay, it's likely to be a fairly simple formatting problem. – Matt Gibson Sep 18 '15 at 19:47
  • `string(19) "2015-09-18 16:22:00"` – Jacobian Sep 18 '15 at 19:49
  • very strange, if you don't quote it then it should be undefined constant. – Fevly Pallar Sep 18 '15 at 19:52
  • @FevlyPallar Yes, but that's actually a warning, not an error. PHP will assume you meant to quote it and still work fine. I'm not saying it shouldn't be fixed, because it should, but it's not what's actually causing the problem here. – Matt Gibson Sep 18 '15 at 19:53
  • @Fevly. Probably, my apache configuration allows to do this. – Jacobian Sep 18 '15 at 19:53
  • Do you know what PDO driver you're using for SQL Server, by any chance? – Matt Gibson Sep 18 '15 at 19:56
  • @Matt. Unfortunatelly, I do not know, BTW I tried `$max_date = date('Y-m-d H:i:s', strtotime($max_date));` before I do binding, but it does not help. – Jacobian Sep 18 '15 at 20:00
  • Out of curiosity, what happens if you try temporarily setting `$max_date = "2015-09-18T16:22:00"` before binding? (Note the "T"; this indicates ISO 8601 date format and should be completely unambiguous to SQL Server.) – Matt Gibson Sep 18 '15 at 20:02
  • @Matt. Hm, If I set my variable with `$max_date = "2015-09-18T16:22:00"`, then it works. – Jacobian Sep 18 '15 at 20:06

2 Answers2

3

I think that your SQL Server may be being confused by the non-standard date format you're using. "2015-09-18 16:22:00" is, of course, a pretty reasonable format, but it's not on the list of standard date literal formats for SQL Server.

So, I'd try using the completely unambiguous ISO 8601 format, formatting your dates as "2015-09-18T16:22:00". PHP has a specific format for ISO 8601, "c", but this is, ironically, not compatible with SQL Server's ISO 8601 format. So, try:

date('Y-m-d\TH:i:s', strtotime($max_date));

Note that I'm not completely sure what's going on, as the date format you're using works OK with SQL Server in general, so perhaps this is related to your specific server configuration (dateformat or language setting?) or with PDO; I don't know too much about the PDO SQL Server driver.

Of course, SQL Server is traditionally used with Microsoft client code, which will bind an actual date object to the date parameter rather than a string, and so you'll never have to worry about the formatting. Not sure if there's a way of doing that with PDO.

Matt Gibson
  • 37,886
  • 9
  • 99
  • 128
  • Thanks, it works with tiny correction. It should be `date('Y-m-d\TH:i:s', strtotime($max_date))`. Whereas, `date('c', strtotime($max_date));` produces values like `2015-09-18T16:22:00+07:00` – Jacobian Sep 18 '15 at 20:14
  • 1
    @Jacobian, With a little note that not every server follows your current configuration, so don't underestimate the quote problem, it's an error in my current PHP version 5.6 – Fevly Pallar Sep 18 '15 at 20:21
  • 1
    @Fevly Pallar . Thank you, Fevly. I will edit my code to make it valid under all configurations. – Jacobian Sep 18 '15 at 20:23
  • 1
    @FevlyPallar Yup, [definitely worth mentioning](http://stackoverflow.com/questions/2405482/is-it-okay-to-use-arraykey-in-php). – Matt Gibson Sep 18 '15 at 20:24
0

MySQL expects the date format YYYY-MM-DD, and I'm not sure what the Oracle format you are getting is. You may want to get PHP to translate the Oracle date using PHP's stringtotime function.

As in:

$maxDate = date('Y-m-d', strtotime($max_date));

The strtotime method is a wonderful little function that takes date information in almost any format and tries to convert it into a usable timestamp.

Thornview
  • 151
  • 3