CODEWITHSUNDEEP
phpsql-serverdatetimestored-procedures
bungdito
bungdito

Reputation: 3620

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

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

Upvotes: 1

Views: 5691

Answers (2)

bungdito
bungdito

Reputation: 3620

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

Upvotes: 0

Groovetrain
Groovetrain

Reputation: 3325

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.

Upvotes: 4

Related Questions