Insert DateTime into SQL Server Using PHP

Question

I'm trying to insert a datetime value into my SQL database using PHP.

This is my code:

$time= date('Y-m-d H:i:s.u');

$ordersquery= "INSERT INTO Orders (customerID, orderDate) VALUES ('{$phonenumber}', '{$time}')";
echo $ordersquery."\n";

Whenever I try to execute the code, there is a failure to enter the information into the database that reads:

INSERT INTO Orders (customerID, orderDate) VALUES ('(444) 849-7592', '2015-05-10 12:03:28.000000')

boolean false

Conversion failed when converting date and/or time from character string.

null

I've also tried using CURRENT_TIMESTAMP and using ISO-8601 format.

I don't know anything about php but I do know sql server. use Parameterized Queries. The link provides a php example. — Zohar Peled
– Zohar Peled, Commented May 10, 2015 at 16:41
Use parameterized queries, or risk a visit from Little Bobby Tables. See PHP examples here. — Matt Johnson-Pint
– Matt Johnson-Pint, Commented May 10, 2015 at 17:54

hatef · Accepted Answer · 2015-05-10 16:50:40Z

5

Try this format instead:

$time= date('Y-m-d H:i:s');

The value to be inserted in Datetime should be in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

answered May 10, 2015 at 16:50

hatef

6,30930 gold badges47 silver badges48 bronze badges

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

9 Comments

Iris Spik Over a year ago

This is what the output is when I use 'YYYY-MM-DD HH:MM:SS 'INSERT INTO Orders (customerID, orderDate) VALUES ('(333) 736-4572', '1969196919691969-DecDec-WedWed 1919:DecDec:stst') boolean false

hatef Over a year ago

@IrisSpik No! Use the part I specified in code section not the explanations below it - I mean this part: $time= date('Y-m-d H:i:s');

Iris Spik Over a year ago

Here'e the output: INSERT INTO Orders (customerID, orderDate) VALUES ('(578) 743-4753', '2015-05-10 13:11:26') boolean false

hatef Over a year ago

@IrisSpik Check your table to see if the row is actually inserted or not.

Iris Spik Over a year ago

it's not inserting. There was some sample data I inserted into the database before- format of the datetime of the variable in the database looks like this '2015-05-09 12:00:00.000'

|

Mangesh Sathe · Accepted Answer · 2015-05-10 16:22:30Z

1

As far as I know CURRENT_TIMESTAMP can store timestamp in format Y-m-d H:m:s. Other than Y-m-d H:m:s It will give an error.

You can set orderDate data type to Varchar and while querying in to database just convert/type cast it to Date format..

Hope this will help or might give you an idea to solve it....

answered May 10, 2015 at 16:22

Mangesh Sathe

2,1835 gold badges26 silver badges42 bronze badges

4 Comments

Iris Spik Over a year ago

This is what I get when I use Current_Timestamp: INSERT INTO Orders (customerID, orderDate) VALUES ('(333) 736-4572', 'CURRENT_TIMESTAMP') boolean false Conversion failed when converting date and/or time from character string. null

Mangesh Sathe Over a year ago

set orderDate data type to Varchar while inserting in db and while querying in to db just convert/type cast it to Date format DATE_FORMAT Y-m-d H:i:s.u

Zohar Peled Over a year ago

@MangeshSatheIND NEVER EVER Store date time values as varchar.

Mangesh Sathe Over a year ago

@ZoharPeled, You are right but iris is trying to store Y-m-d H:i:s.u in field having data type CURRENT_TIMESTAMP Y-m-d H:m:s , i thnk this is wrong. Data type Varchar will be just another way to achieve his task...

Collectives™ on Stack Overflow

Insert DateTime into SQL Server Using PHP

2 Answers 2

9 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related