DATE ISSUE. MONTH AND DAY EXCHANGED.

[JORGEMAL]

I wonder if this is a DotConnect issue so please let me know for further investigation.

I am inserting a date field into a table using the (year, month day) overload of PgSqlTimeStamp but when I go and check the record I get month and day exchanged.

For example, if I specify July 1st, 1940 I get January 7th, 1940.

Also, if I specify a date like January 31, 1940 I get the following exception:

date/time field value out of range: "31/01/1940 AD"
PgSqlException
at Devart.Data.PostgreSql.PgSqlDataReader.e(Int32 A_0)
at Devart.Data.PostgreSql.PgSqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at Artista.Modifica(String& strTipoMensaje, Int16& intTotalMensajes, String[,] arrMensajes, String strOrigen)


Please advice.

[Shalex]

It seems like date format mismatch on your client side (the regional settings of Windows) and your database (the datestyle setting in postgresql.conf).

I have checked the 4.65.57 version of dotConnect for PostgreSQL with the following code:

Code: Select all

    using (PgSqlConnection conn = new PgSqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ConnectionString))
    {
        conn.Open();
        PgSqlCommand command = conn.CreateCommand();
        command.CommandText = "insert into timetable (datecolumn) values (@datecolumn)";
        PgSqlParameter param = new PgSqlParameter();
        param.ParameterName = "@datecolumn";
        param.PgSqlType = PgSqlType.Date;
        param.Value = DateTime.Now.Date;
        command.Parameters.Add(param);

        command.ExecuteNonQuery();
    }

Here is a script of timetable from my database at PostgreSQL Server 8.4:

Code: Select all

CREATE TABLE timetable
(
  datecolumn date
)

Please tell us how should we modify this sample to reproduce the problem? Also please tell us the date format at your client and database side.

[JORGEMAL]

It seems like date format mismatch on your client side (the regional settings of Windows) and your database (the datestyle setting in postgresql.conf).

I have checked the 4.65.57 version of dotConnect for PostgreSQL with the following code:

Code: Select all

    using (PgSqlConnection conn = new PgSqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ConnectionString))
    {
        conn.Open();
        PgSqlCommand command = conn.CreateCommand();
        command.CommandText = "insert into timetable (datecolumn) values (@datecolumn)";
        PgSqlParameter param = new PgSqlParameter();
        param.ParameterName = "@datecolumn";
        param.PgSqlType = PgSqlType.Date;
        param.Value = DateTime.Now.Date;
        command.Parameters.Add(param);

        command.ExecuteNonQuery();
    }

Here is a script of timetable from my database at PostgreSQL Server 8.4:

Code: Select all

CREATE TABLE timetable
(
  datecolumn date
)

Please tell us how should we modify this sample to reproduce the problem? Also please tell us the date format at your client and database side.

All of my code to insert a record is within a class so I prepare the date information as follows before calling my class insert method:
Artista.FechaNac = new PgSqlTimeStamp(dteFechaNac.Date.Year, dteFechaNac.Date.Month, dteFechaNac.Date.Day);

Where dteFechaNac is an ASPX control that displays a calendar and the user can select a date.
Also, "Artista" is the name of my class and "FechaNac" is a static property. As you can see, I use the "year, month, day" overload of PgSqlTimeStamp.

My table field is of DATE type.

I have reviewed my Postgresql.conf and I have the following entry:
datestyle = 'iso, mdy'

Also, I checked the regional settings in my PC and the short date format is set to dd/MM/yyyy. I tried changing it to MM/dd/yyyy but I have the same result.

Respectfully,
Jorge Maldonado

[Shalex]

1. Tell us your current versions of dotConnect for PostgreSQL (x.xx.xx) and PostgreSQL Server (x.x.x).
2. Are you using protocol 2.0 or 3.0? Post here your connection string.
3. Could you please execute this code? Does it work correctly?

Code: Select all

    using (PgSqlConnection conn = new PgSqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ConnectionString))
    {
        conn.Open();
        PgSqlCommand command = conn.CreateCommand();
        command.CommandText = "insert into timetable (datecolumn) values (@datecolumn)";
        PgSqlParameter param = new PgSqlParameter();
        param.ParameterName = "@datecolumn";
        param.PgSqlType = PgSqlType.Date;
        param.Value = new PgSqlTimeStamp(2009, 1, 31);;
        command.Parameters.Add(param);

        command.ExecuteNonQuery();
    }

The script for timetable is above.

[JORGEMAL]

1. Version of dotConnect for PostgreSQL: 4.0.12.0
Version of PostgreSQL: 8.3.1

2. I am using Protocol 2.0, here is the connection string:


3. The code you sent executes correctly so now I am sure the problem has to do with me.
Everything was working fine until I changed my development PC but I basically installed it and configured it as usual.

I do not really know where to look for an answer.
I will very much appreciate any advice.

Respectfully,
Jorge Maldonado

1. Tell us your current versions of dotConnect for PostgreSQL (x.xx.xx) and PostgreSQL Server (x.x.x).
2. Are you using protocol 2.0 or 3.0? Post here your connection string.
3. Could you please execute this code? Does it work correctly?

Code: Select all

    using (PgSqlConnection conn = new PgSqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ConnectionString))
    {
        conn.Open();
        PgSqlCommand command = conn.CreateCommand();
        command.CommandText = "insert into timetable (datecolumn) values (@datecolumn)";
        PgSqlParameter param = new PgSqlParameter();
        param.ParameterName = "@datecolumn";
        param.PgSqlType = PgSqlType.Date;
        param.Value = new PgSqlTimeStamp(2009, 1, 31);;
        command.Parameters.Add(param);

        command.ExecuteNonQuery();
    }

The script for timetable is above.

[Shalex]

1. We recommend you to use Protocol 3.0 (the Protocol=Ver30; connection string parameter). This should help. Why did you decide to choose the 2.0 protocol in your project?
2. Make sure that you use the PgSqlTimeStamp(year, month, day) overload in your code correctly.

If this doesn't help, please send us a small test project with the DDL script to reproduce the error in our environment.

[JORGEMAL]

I changed protocol to v30 without success. I do not really know why I was using protocol v20, I guess because of some tests I did in the past on queries with several results.

I tried to attach a file but I did not find how so I would like to send you and email with a document where I include code from my app for your review; please tell me an address.

Anyway, I am considering installing my machine again because this issue started when I changed my development PC and I have noticed some "very small and almost insignificant" strange behavior in my Visual Studio.

ADDITIONAL COMMENTS:
* I created my database with ENCODING: WIN1252 (for western languages) because UTF-8 restricts me from using some characters like Ñ for example.

I appreciate your support.

Jorge Maldonado

1. We recommend you to use Protocol 3.0 (the Protocol=Ver30; connection string parameter). This should help. Why did you decide to choose the 2.0 protocol in your project?
2. Make sure that you use the PgSqlTimeStamp(year, month, day) overload in your code correctly.

If this doesn't help, please send us a small test project with the DDL script to reproduce the error in our environment.

[Shalex]

Please try to localize the problem in a small project. My e-mail address is (alexsh at devart*com).