Date flips day/month when saving to MS SQL Server

[A_Fejer]

Hi,

I just started developing with the SmartClient a few days ago and I'm having some trouble with the Date type saving the correct values in a SQL Server.

I'm trying to build an editable ListGrid, where you can add/delete entries and edit the entries by doubleclicking. For the dates I have defined them as type:date to be able to use that datechooser widget. After the editing is done, it should automatically update the database entry with the changed values. The values loaded from the database and the value (and validation by the field) while editing the field are correrct, but the value saved in the database is not correct.
If I try to save today's date (16.02.2012) the field's validation accepts the input, but the SQL Server returns an error saying the datetime gotten from converting the varchar type is out of bounds.

It seems like the SQL Server expects a different format for the date-string for the its update statement.
Is there a way to change the format of the date-string (after being done with editing the cell and) before it is sent to the database?

SmartClient version:
SC_SNAPSHOT-2012-02-08_v8.2p
Browser:
Internet Explorer 6.0
Server logs:
Server Log.txt (attachment)
RPC response:
RPC log.txt (attachment)
JavaScript StackTrace:
No Errors

Sample code:
Datasource XML:

<DataSource 
	schema="dbo"
	dbName="mssql1"
	tableName="Provisionsstammdaten"
	ID="provisionsstammdaten_v1"
	serverType="sql"
>
    <fields>
        <field name="LFDNR"        	type="sequence"		title="Laufende Nr."	hidden="true"	primaryKey="true"		/>
        <field name="KDNR"			type="integer"		title="Kundennr."		required="true"		/>
		<field name="ARTNR"   		type="text"			title="Artikelnr."							/>
		<field name="PREIS"   		type="floatdec"		title="Preis"     							/>
		<field name="ABSCHLAG"   	type="floatdec"		title="Abschlag"     						/>
		<field name="TYP"   		type="text"			title="Typ"     							/>
		<field name="FIRMA"   		type="text"			title="Firma"     							/>
		<field name="ERLANGEN"   	type="floatdec"		title="Erlangen"     						/>
		<field name="ZAT"   		type="floatdec"		title="ZAT"     							/>
		<field name="GUELTIG_VON"   type="date"			title="Gültig von"	/>
		<field name="GUELTIG_BIS"   type="date"   		title="Gültig bis"	/>	
    </fields>
</DataSource>

ListGrid from the JavaScript File:

Date.setShortDisplayFormat("toEuropeanShortDate");
Date.setInputFormat("DMY");

isc.ListGrid.create({
    ID: "listgrid_provisionsstammdaten",
    width:"100%", height:"100%",
	dataSource: provisionsstammdaten_v1,
	autoFetchData:true,
	canEdit:true,
	modalEditing: true,
	autoDraw:false,
	fields: [
	{name:"KDNR", width:100, align:"right"},
	{name:"ARTNR", width:100, align:"right"},
	{name:"PREIS", width:100, align:"right", 
		formatCellValue:function (value) {
             if (isc.isA.Number(value)) {
                return value.toCurrencyString(" ",",");
             }
             return value;
         }
    },
	{name:"ABSCHLAG", width:100, align:"right"},
	{name:"TYP", width:100, align:"right"},
	{name:"FIRMA", width:100, align:"right"},
	{name:"ERLANGEN", width:100, align:"right"},
	{name:"ZAT", width:100, align:"right"},
	{name:"GUELTIG_VON", width:100, type:"date"},
	{name:"GUELTIG_BIS", width:100, type:"date"}
	],
	dataPageSize:100
})

I have noticed 2 similar older topics and I tried to extend the server properties file by using the code provided in the answer, but it has no effect.
http://forums.smartclient.com/showthread.php?t=14188
http://forums.smartclient.com/showthread.php?t=14727

sql.mssql1.defaultDateFormat: yyyyMMdd
sql.mssql1.defaultDateTimeFormat: yyyyMMdd HH:mm:ss

[Isomorphic]

Your server log shows this SQL being run for an update, and no error is reported by the server:

=== 2012-02-16 17:34:30,265 [sor2] INFO SQLDriver - [builtinApplication.provisionsstammdaten_v1_update] Executing SQL update on 'mssql1': UPDATE dbo.Provisionsstammdaten SET GUELTIG_VON='2012-02-09 00:00:00' WHERE (Provisionsstammdaten.LFDNR='1')

So what specifically is going wrong?

Also, what's the underlying column type? If its some CHAR type rather than a date, you can use dataSourceField.sqlStorageStrategy to define how dates are stored in a CHAR column.

[A_Fejer]

So what specifically is going wrong?

The problem is the format the date is being saved in.
What I need (and what the Smart Client uses) is the following format:
"dd.MM.yyyy"
But the database saves the date formatted as "MM.dd.yyyy".
This means if I choose a date (i.e. 07.02.2012 -> dd.MM.yyyy) with the date widget of the field, the database swaps the day and the month (to 02.07.2012 -> MM.dd.yyyy).
If I change the date input format from 'DMY' to "MDY", then the resulting date would be correct for the database, but datefield's validation tells me that the entry is not correct.

So I need some method or something to be able to change the format of the date after I'm done editing the field and before it's sent to the database.

Also, what's the underlying column type? If its some CHAR type rather than a date, you can use dataSourceField.sqlStorageStrategy to define how dates are stored in a CHAR column.

The column type in the database table is a datetime, but the time is not relevant.

[Isomorphic]

If you want to affect the date format in the UI, see the APIs on DateUtil.

You have already shown the server.properties settings that would allow you to reverse MDY / DMY order for database inserts - so is something not working when you use these settings?

[A_Fejer]

If you want to affect the date format in the UI, see the APIs on DateUtil.

I'm not trying to change the date format in the UI. It's the correct format (dd.MM.yyyy) for Europe. I would rather like to change the format of the date in the update statement.

You have already shown the server.properties settings that would allow you to reverse MDY / DMY order for database inserts - so is something not working when you use these settings?

It doesn't seem to have an effect on the application.

I tried using 'yyyyMMdd', 'dd.MM.yyyy', 'DMY' for the defaultDateFormat setting, the application still doesn't behave the way I would want it to.

Are there any documented examples which show the results of those server.properties settings?


EDIT:
I tried using a different JDBC driver since I had been using the JTDS driver for my application, but even with the sqljdbc4.jar driver from Microsoft I got the same result.

I also tried to manually enter the SQL statement shown in the Server Logs with the Microsoft SQL Management Studio:

UPDATE dbo.Provisionsstammdaten SET GUELTIG_VON='2012-02-09 00:00:00' WHERE (Provisionsstammdaten.LFDNR='1')

In this case the date saved in the table is correct (the entry shows '2012-02-09 00:00:00.000').

When I change the Date-String to the format which is being used by the date widget in the ListGrid to show the value, the database swaps the day and month:

UPDATE dbo.Provisionsstammdaten SET GUELTIG_VON='09.02.2012' WHERE (Provisionsstammdaten.LFDNR='1')

In this case the date saved in the table is not correct (the entry shows '2012-09-02 00:00:00.000').

[Isomorphic]

This suggests that you can have some kind of JDBC driver setting present which is causing dates Strings to be interpreted as though a European end user were typing the dates in instead of an automated system expecting a standardized order. You might start here, which is a doc on how you can configure this from SQL.

[A_Fejer]

Thanks for the suggestion. It turned out that the SQL Server user's settings are causing the problem.
The user being used for the SmartClient connection has German for its standard language.
It seems that for every query the SmartClient logs in to the database and logs out when it's done. With each login the server chooses the default datetime format of the language (unless the date format is overridden with the SET DATEFORMAT command).

In this case a user with German as the default language will get the following result:

UPDATE dbo.Provisionsstammdaten SET GUELTIG_VON='2012-02-09 00:00:00' WHERE (Provisionsstammdaten.LFDNR='1')	
--Result: 2012-09-02 00:00:00.000

And a user with English as the default language will get the following result:

UPDATE dbo.Provisionsstammdaten SET GUELTIG_VON='2012-02-09 00:00:00' WHERE (Provisionsstammdaten.LFDNR='1')
--Result: 2012-02-09 00:00:00.000

The reason why I got the correct result while checking the UPDATE statement with the SQL Management Studio was because I've been doing that with a different user, which had English as the default language.

It is clear that the SmartClient is using the ANSI SQL Standards for DATETIME and INTERVAL values. If the date format in the UPDATE statement is changed to the ISO 8601 standard which adds the letter 'T' as a delimiter between the date and time, then no matter what language the statement will save the correct date.

UPDATE dbo.Provisionsstammdaten SET GUELTIG_VON='2012-02-09T00:00:00' WHERE (Provisionsstammdaten.LFDNR='1')
--Result: 2012-02-09 00:00:00.000

Now as a workaround I have the following changes in my datasource XML file, to insert the letter 'T':

<field name="GUELTIG_VON"   type="datetime"		title="Gültig von"> 
	<customInsertExpression>LEFT($values.GUELTIG_VON,10)+'T'+RIGHT($values.GUELTIG_VON,8)</customInsertExpression>
	<customUpdateExpression>LEFT($values.GUELTIG_VON,10)+'T'+RIGHT($values.GUELTIG_VON,8)</customUpdateExpression>
</field>
<field name="GUELTIG_BIS"   type="datetime"   	title="Gültig bis">	
	<customInsertExpression>LEFT($values.GUELTIG_BIS,10)+'T'+RIGHT($values.GUELTIG_BIS,8)</customInsertExpression>
	<customUpdateExpression>LEFT($values.GUELTIG_BIS,10)+'T'+RIGHT($values.GUELTIG_BIS,8)</customUpdateExpression>	
</field>

[Isomorphic]

Thanks, we'll check this out - do you happen to know if there are older versions of SQL Server which would reject the ISO 8601 date format?

[A_Fejer]

After a quick search I managed to find out that the ISO 8601 standard is supported only from SQL Server 2000 onwards.

I found a conversation about the SQL Server not supporting all the attributes of the ISO 8601 like timezones for example.

In my case I'm using the SQL Server 2008 version, but without any timezone attributes.

[Isomorphic]

Thanks for letting us know about this. When we put in support for using the ISO date format we'll also add a version check.

[jvopni]

Has this support been implemented yet? We have the same issue using SmartGWT 3.0 with a German SQLServer and German OS. I realize we can use custom Insert/Update expressions but would prefer not to do it that way as we have many datetime fields.

[Isomorphic]

Yes this was taken care of in 3.1.

[jvopni]

Is there any way to force this format in 3.0? Our first attempt at using 3.1 generated some run time issues and we weren't planning on upgrading at this time due to time constraints

[Isomorphic]

You should really update to 3.1. Now that 3.1 has been available for quite some time, 3.0 is receiving very limited patches.

We're not sure when you attempted the move to 3.1, but whatever these runtime exceptions were, they might have been corrected by a patch by now (or might have just been faulty installation, or usage issues you'll need to correct anyway).