Error when trying to insert date into datetime column

Question

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

For heaven's sake, us parameterized queries

Joe Phillips
– Joe Phillips

2009-10-28 16:04:14 +00:00
Commented Oct 28, 2009 at 16:04 — Joe Phillips
– Joe Phillips, Commented Oct 28, 2009 at 16:04

Maximilian Mayerl · Accepted Answer · 2009-10-28 15:59:09Z

1

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)";

answered Oct 28, 2009 at 15:59

Maximilian Mayerl

11.4k3 gold badges37 silver badges42 bronze badges

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

1 Comment

doubleplusgood Over a year ago

Thanks man, CURRENT_TIMESTAMP works perfectly. It's weird how it worked when we were using a different sql server (albeit using the same version and the exact same database).

TigerTiger · Accepted Answer · 2009-10-28 16:02:31Z

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.

tbraun89 · Accepted Answer · 2012-11-13 15:26:10Z

1

I had the same problem, possible this solution will be helpful: check that you have a yyyy-DD-mm date format (not yyyy-mm-DD), because by default, mssql accept date in yyyy-DD-mm format.

edited Nov 13, 2012 at 15:26

tbraun89

2,2343 gold badges26 silver badges47 bronze badges

answered Aug 26, 2011 at 9:14

Leonid Shagabutdinov

112 bronze badges

Comments

Alexey · Accepted Answer · 2009-10-28 16:04:37Z

0

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)";

answered Oct 28, 2009 at 16:04

Alexey

1583 silver badges10 bronze badges

Collectives™ on Stack Overflow

Error when trying to insert date into datetime column

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related