Error when trying to insert date into datetime (php & SQL Server stored procedure)

Question

I have the same problem but a little different from Neil problems (Error when trying to insert date into datetime column),

Neil want to get now date time so he can use CURRENT_TIMESTAMP or GETDATE().

My question is, how if I have string of date like:

$date = '2011-03-29';

then I want to insert into SQL Server database using a stored procedure with PHP.

 insert into tbl(date) 
 values($date)

Can anybody help? Thanks

Just fyi, I have

SQL Server table:

CREATE TABLE tbl
(
    date datetime
)

Stored procedure:

create procedure sp_insertdate
    (@date datetime)
as
begin
    insert into tbl(date) values(@date)
end

PHP code:

<?php

  $date = '2011-03-29';  

  include("con.php");  
  $query  = mssql_init("sp_insertdate");
  mssql_bind($query, "@date", &$date, SQLINT4); //sqlint4 for datetime

  mssql_execute($query);

?>

The result is 1900-1-1,

I have tried to send the varchar (in php) first then convert to the datetime (in stored procedure) but there is some error.

Really I'm stuck.. any idea for this problem?

I just want to insert string 2011-03-29 into a SQL Server datetime column using a stored procedure from PHP. I'm sorry because I can't speak English fluently

Groovetrain · Accepted Answer · 2011-03-23 13:29:44Z

4

Whenever I've had a problem like this (using mysql), I just had to add the time (midnight) onto the date so that it can recognize it as the datetime that it's expecting. Try this:

$date = '2011-03-29';
$date .= ' 00:00:00';

Then process the rest as you would. This works for mysql, maybe sql-server needs it like this too.

answered Mar 23, 2011 at 13:29

Groovetrain

3,32521 silver badges23 bronze badges

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

Comments

bungdito · Accepted Answer · 2011-03-24 02:27:36Z

0

i got answer from my friends,

just put mssql_bind($query, "@date", &$date, SQLCHAR); //not sqlint4 for datetime but just sqlchar or sqlvarchar

however many thanks to Groovetrain, i can put also $date='2011-03-29 00:00:00' then put

mssql_bind($query, "@date", &$date, SQLCHAR,false,false,19); //19 length of date string

answered Mar 24, 2011 at 2:27

bungdito

3,6204 gold badges33 silver badges38 bronze badges

Collectives™ on Stack Overflow

Error when trying to insert date into datetime (php & SQL Server stored procedure)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related