I use SQL server with datatype date.

SQL Server comes with the following data types for storing a date or a date/time value in the database:

DATE - format YYYY-MM-DD

I like to insert/update Dutch date 01-12-2016 (1 december 2016). It will be stored as 12-01-2016 (12 december 2016). If I try to insert/update 13-12-2016 (13 december 2016) it stores nothing because 12-13-2016 year contains 12 months.

With classic ASP I use SET DATEFORMAT DMY in my SQL insert/update statement. This works fine and 01-12-2016 (1 december 2016) will be stored as 2016-12-01 in the SQL database.

<
%If (CStr(Request("update")) = "form1") Then

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open string


autoID= Request("autoID")
datum = Request("datum")
	
    SQL = "SET DATEFORMAT DMY; UPDATE tbl_test " _
        & "SET datum = " & datum _
	& " WHERE autoID = " & autoID
    Conn.Execute SQL
Next 
%>

How can I create a workaround with DMXzone Database Updater to insert/update Dutch dates?

Hello,
How exactly are you inserting the date? How do you enter the date in the field, which gets inserted?

Hi Teodor,

I send mail with a live link to you.

Insert page contains textfield with jquery datepicker. when inserting 01-06-2016 to the textfield it will be inserted as 06-01-2016 to the SQL database

I have not received your link yet, but from what i see in your post i guess what is wrong. The date-picker you are using, is using a date format different than the one in your DB, which breaks the way it is inserted.

That is why our dmxzone calendar 3 has an "alt field" option, which is used to set the date in the format it should be inserted. So you just set two date formats there:
- dd-mm-yyyy for the frontend - what is displayed on the page
- yyyy-mm-dd (which is the default mssql db data format) for the alt field, which inserts the selected date.

another strange thing

If I attach a Databinding formatter Format Date (dd-mm-yyyy) to the datefield from the multiple update testpage. The update doesn't work.

updater not working with databinding formatter


If i remove the databinding formatter. I get a date like 2016-10-05T16:00:00Z but now the multiple updater works fine.

updater works without databinding formatter

The data bindings formatter, as it name suggests, does only work with data bindings, not with the server connect.
The Server Connect Formatter will be released in a couple of weeks, and then you will be able to format the inserted date format, directly in the insert/update record steps.

The whole issue seems to be caused you are trying to insert dates, in a format different than the default one for your DB.

SQL datatype date stores date as YYYY-MM-DD.

So when insert/update a Dutch date like 1 december 2016 (01-12-2016 in the textfield form) it will reverse day and month and insert/update 12 januari 2016 (12-01-2016).



It stores 2016-01-12 (yyyy-mm-dd) in my SQL database

My workaround in classic asp is using SET DATEFORMAT DMY in SQL insert/update statement.

I hope the server connect formatter can solve this issue.

The problem is that the date format in your database is:

YYYY-MM_DD

and you put in a value which is different: DD-MM-YYYY .... which breaks the things in the databse. AS i explained this is already taken care of in DMXzone Calendar 3, where you have an alt (hidden) field which is formatted as your database and you insert it.
The server connect formatter will also allow you to do this, on insert.

Hi Teodor,

So when I buy DMXzone Calendar 3 this problem is solved?

Yes, it has been integrated into it for exactly this reason.

Hi Teodor,

I just purchased Calandar 3. I will look further into it.

thanks

Okay - so just define an alt field in its options, and make its format the same as your database. Then in the insert record, use it (hidden_calendar_field) for the value for dates. And then in the calendar format (what is displayed on the page) use whatever you want.

Hi Teodor,

It works. insert/update Dutch date into SQL server database with DMXzone Calendar 3.

Thanks.

Great, that you managed to fix the problem!

Hi Teodor,

I purchased DMXzone Server Data Formatter as you mentioned earlier.

www.dmxzone.com/support/23014/topic/61401/

How can I combine this with Calendar 3 to get the value from my date/time database field attached with Calendar 3 textfield.

So the initial date needs to get date/time value from my database.



so the calendar 3 textfield keeps empty when click on a link.

Hello,
I am not sure i understand your last question? What exactly do you mean by:

Quote

How can I combine this with Calendar 3 to get the value from my date/time database field attached with Calendar 3 textfield.
So the initial date needs to get date/time value from my database.
so the calendar 3 textfield keeps empty when click on a link.

When click for example on link 1 october 2016

date textfield value (Calendar 3) keeps empty
subject value textfield value filled with 1 october 2016

so textfield with data bindings works but textfield with date doesn't how can I bind this????

When binding i get this value: 2016-09-30T22:00:00Z But I need to Format this to dutch dd-mm-yyyy

I mail you the link

Well, on the clientside you need to use the data bindings formatter .. Just format the initial date in the format you need it, just like that:

Hi Teodor,

So far this works

Only I had to add data-binding-value="{{datum.formatDate( "dd-MM-yyyy" )}}" handcoded. In combination with Calendar 3 HTML5 Data bindings icon will not open and gives an error Nothing Selected.

In my design screen I see an empty textfield (no text between brackets)

What exactly do you mean by:" In combination with Calendar 3 HTML5 Data bindings icon will not open " ?
It is located in the properties inspector ...

Oke clear.

Now I get:

<input name="datum" class="dmxCalendar2" id="datum" value="{{datum.formatDate( &quot;dd-MM-yyyy&quot; )}}" />

Is this correct because other texfield uses data-binding-value instead of value?

Yes it is correct.