It seems that the datetime format can differ from a server to another... but I see nothing in MS SQL Administrator to define a specific format for a whole database, a table or even a table field.

You could tell that it is a mssql problem but it seems that it can be avoid by using the command :
"SET DATEFORMAT YMD"
before executing queries that involves dates. It specifies that the format of the datetime field respects the ISO format :"yyyy-mm-dd hh:mm:ss"
(I tried in SQL Management Studio : it works)
Another solution could be to cast every datetime to be sure of its format or use the odbc notation : {ts 'yyyy-mm-dd hh:mm:ss'}

We have had the same problem in German MSSql environment.

Our quick and dirty fix was to modify the database connect functions. We added in configuration files the entry "$g_db_session_init = 'SET DATEFORMAT YMD';" and expanded the database connect functions. See below:

File: $Id: database_api.php,v 1.55 2007/04/22 07:45:34 vboctor Exp $

# Make a connection to the database
function db_connect( $p_dsn, $p_hostname = null, $p_username = null, $p_password = null, $p_database_name = null ) {
    global $g_db_connected, $g_db, $g_db_session_init; // modified

    if(  $p_dsn === false ) {
        $g_db = ADONewConnection( config_get_global( 'db_type' ) );
        $t_result = $g_db->Connect($p_hostname, $p_username, $p_password, $p_database_name );
    } else {
        $g_db = ADONewConnection( $p_dsn );
        $t_result = $g_db->IsConnected();
    }

    if ( !$t_result ) {
        db_error();
        trigger_error( ERROR_DB_CONNECT_FAILED, ERROR );
        return false;
    }

    $g_db_connected = true;

    // ****************************************************************
    // modified
    // ****************************************************************
    if ( $g_db_session_init !='') {
        db_query( $g_db_session_init );
    }

    return true;
}

# --------------------
# Make a persistent connection to the database
function db_pconnect( $p_dsn, $p_hostname = null, $p_username = null, $p_password = null, $p_database_name = null ) {
    global $g_db_connected, $g_db, $g_db_session_init; // modified

    if(  $p_dsn === false ) {
        $g_db = ADONewConnection( config_get_global( 'db_type' ) );
        $t_result = $g_db->PConnect($p_hostname, $p_username, $p_password, $p_database_name );
    } else {
        $g_db = ADONewConnection( $p_dsn );
        $t_result = $g_db->IsConnected();
    }

    if ( !$t_result ) {
        db_error();
        trigger_error( ERROR_DB_CONNECT_FAILED, ERROR );
        return false;
    }
    $g_db_connected = true;

    // ****************************************************************
    // modifiziert
    // ****************************************************************
    if ( $g_db_session_init !='') {
        db_query( $g_db_session_init );
    }

    return true;
}

I think that using ODBC notation {ts 'yyyy-mm-dd hh:mm:ss'} is the best solution.

I also get this error using mysql in Mantis 1.2 but not in 1.1

The difference appears to be :-
trunk (1.2)

--------------------

# return current timestamp for DB
function db_now() {
    global $g_db;

    return $g_db->DBTimeStamp(time());
}

1.1

--------------------

# return current timestamp for DB
function db_now() {
    global $g_db;

    return $g_db->DBTimeStamp(time());
}

Changing to use the 1.1 code cures the insert into mantis_user_table (uses db_query),
but I then get an error inserting into mantis_email_table (uses db_query_bound).

Nick

I changed to using db_query_bound and it works with mysql
Test done :-
added user into mysql database using European date format on 20th of Jan.

I am attaching my patched code.
This needs testing using other databases (and other non-US date formats ?).

I've checked the latest code and it seems that we already use db_query_bound() here? Is the issue still reproducible on the latest code?

I've noticed that there is still once db_query() in this file in method user_get_logged_in_user_ids(). However, this is another issue.

For now I will target this for 1.x.x rather than 1.2.x.

MantisBT currently supports Mysql and has support for other database engines.

The support for other databases is known to be problematic.

Having implemented the current database layer into Mantis 10 years ago, I'm currently working on replacing the current layer.

If you are interested in using Mantis with non-mysql databases - for example, Oracle, PGSQL or MSSQL, and would be willing to help out testing the new database layer, please drop me an email at paul@mantisforge.org

In the meantime, I'd advise running Mantis with Mysql Only to avoid issues.

Thanks
Paul