×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

PHP and MSSQL dates

PHP and MSSQL dates

PHP and MSSQL dates

(OP)
I am having to update a system I built from mysql to mssql (and also from a wamp environment to IIS running windows php).

All is going well so far apart from datetime data types. I read that datetime is not supported and to use the much more forgiving datetime2 instead but I'm still getting the error

Array ( [0] => Array ( [0] => 22007 [SQLSTATE] => 22007 [1] => 241

CODE

=> 241 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string. ) ) 

Removing the date fields from my insert query works fine. Here's my test script

[code]
if (isset($_POST['submit'])) {

$id = $_SESSION["user_id"]
$varcharfield1 = $_POST['varcharfield1'];
$varcharfield2 = $_POST['varcharfield2'];
$datetimefield = $_POST['datetimefield'];

$params = array($varcharfield1, $varcharfield2, $datetimefield, $id);

$query = sqlsrv_query( $conn, "update mytable set 
varcharfield1=?, 
varcharfield2=?, 
datetimefield=?
where id=?", $params);

} 

Thanks for any suggestions

RE: PHP and MSSQL dates

first we don't know what format the POST variable is in. so it's a bit difficult to suggest a fix. Let's assume it is in yyyy-mm-dd format.

warning - i know precious little about sql server.

CODE

$date = new DateTime($_POST['datetimefield'], new DateTimeZone('UTC'));
$params = array($varcharfield1, $varcharfield2, $date->format(DateTime::ISO8601), $id);
$query = sqlsrv_query( $conn, "update mytable set 
varcharfield1=?, 
varcharfield2=?, 
datetimefield=CONVERT(datetime, ?,126)
where id=?", $params); 

possibly you could bypass the conversion by outputting the date in the format

CODE

$date->format("Y-m-d\TH:i:s"); 

RE: PHP and MSSQL dates

(OP)
Thanks for the suggestions, I ended up using format when populating the text field value when printed to the page and

CODE

date("Y-m-j", strtotime(str_replace('/', '-', $_POST['datetimefield']))); 

on inserting data.

Another question, is that safe to use the above code in my query, for other fields I prevent injection by running through a function?

Thanks

RE: PHP and MSSQL dates

if you use the placeholder syntax I used above then the engine does the escaping transparently. that said you should always validate incoming data before you use it.

so:

1. check that the date string looks like a date and is within an acceptable range.
2. and that the two other fields are as you expect.

for the datetime field, you can make the code a bit neater

CODE

try{
$param[2] = datetime::createFromFormat('Y/m/j',$_POST['datetimefield'])->format('Y-m-d\TH:i:s');
} catch {Exception $e) {
 print_r($e);
} 

the reason you need a try ... catch block is if the format of the incoming date is altered then the code will fail with a fatal error.

This code does not validate that a date is well formed. e.g. if you provide '2015/01/70' php will gladly take that and make it into 11th March (being 70 days after jan 1st). so even with the improvement in date handling through the use of the built-in classes - you are still left having to use some other code to validate the types of incoming fields.

I typically do all of that in my class model. then you end up with a nice encapsulation.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close