Error when trying to insert date into datetime column

Question

Hi there,

I have a form that is trying to insert some data into an SQL Server 2008 database. The form has a function to get the current date/time and then insert it into the database as follows;

$now = date("Y-m-d H:i:s");
$q = "INSERT INTO ".TBL_USERS." ( username, password, userid, userlevel, email, created, updated, timestamp, fullname, avatar )
VALUES ( '$username', '$password', '0', $ulevel, '$email', '$now', '$now', $time, '$fullname', $avatar)";

However, when the form submits it reports an error of;

Warning: mssql_query() [function.mssql-query]: message: Cannot insert the value NULL into column 'created', table 'dbo.users'; column does not allow nulls. INSERT fails.

We have done an echo $q to show the data trying to be inserted and it does show the correct datetime (e.g. 2009-10-28 15:43:00.000), the .000 gets added by the db normally.

If I manually create a record in the database, the datetime in the example above is accepted.

Wondered if anyone had come across this issue before?

Thank you. Neil

Answer 1

Why is $now..inside single quotes?

Answer 2

Try NOW() if you are trying to add current timestamp.

    $q = "INSERT INTO ".TBL_USERS." ( username, password, userid, userlevel, 
email, created, updated, timestamp, fullname, avatar )
    VALUES ( '$username', '$password', '0', $ulevel, 
'$email', NOW(), NOW(), $time, '$fullname', $avatar)";

UPDATE

oh its sql server

probably you'd use the CURRENT_TIMESTAMP then

SELECT SYSDATETIME()
    ,SYSDATETIMEOFFSET()
    ,SYSUTCDATETIME()
    ,CURRENT_TIMESTAMP
    ,GETDATE()
    ,GETUTCDATE();
/* Returned:
SYSDATETIME()      2007-04-30 13:10:02.0474381
SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME()   2007-04-30 20:10:02.0474381
CURRENT_TIMESTAMP  2007-04-30 13:10:02.047
GETDATE()          2007-04-30 13:10:02.047
GETUTCDATE()       2007-04-30 20:10:02.047

you can see that current_timestamp gives back date alongwith time.

Answer 3

Try CURRENT_TIMESTAMP instead of '$now' like so:

$q = "INSERT INTO ".TBL_USERS." ( username, password, userid, userlevel, email, created, updated,   timestamp, fullname, avatar )
VALUES ( '$username', '$password', '0', $ulevel, '$email', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, $time, '$fullname', $avatar)";

Answer 4

MSSQL Server has GETDATE() function for current datetime, so try:

$q = "INSERT INTO ".TBL_USERS." ( username, password, userid, userlevel, email, created, updated, timestamp, fullname, avatar )
VALUES ( '$username', '$password', '0', $ulevel, '$email', GETDATE(), GETDATE(), $time, '$fullname', $avatar)";