DMXzone Database Updater ASP Support Product Page


How to insert/update Dutch date into SQL or Access database

Reported 14 Jun 2016 12:11:04
has this problem
14 Jun 2016 12:11:04 MX Addict posted:
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

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


Replied 14 Jun 2016 12:24:39
14 Jun 2016 12:24:39 Teodor Kuduschiev replied:
How exactly are you inserting the date? How do you enter the date in the field, which gets inserted?
Replied 14 Jun 2016 12:42:41
14 Jun 2016 12:42:41 MX Addict replied:
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
Replied 14 Jun 2016 12:57:10
14 Jun 2016 12:57:10 Teodor Kuduschiev replied:
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.
Replied 14 Jun 2016 13:00:11
14 Jun 2016 13:00:11 MX Addict replied:
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
Replied 14 Jun 2016 13:02:28
14 Jun 2016 13:02:28 Teodor Kuduschiev replied:
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.
Replied 14 Jun 2016 13:28:02
14 Jun 2016 13:28:02 MX Addict replied:
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.
Replied 14 Jun 2016 13:31:13
14 Jun 2016 13:31:13 Teodor Kuduschiev replied:
The problem is that the date format in your database is:


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.
Replied 14 Jun 2016 13:35:36
14 Jun 2016 13:35:36 MX Addict replied:
Hi Teodor,

So when I buy DMXzone Calendar 3 this problem is solved?
Replied 14 Jun 2016 13:38:00
14 Jun 2016 13:38:00 Teodor Kuduschiev replied:
Yes, it has been integrated into it for exactly this reason.
Replied 14 Jun 2016 13:50:47
14 Jun 2016 13:50:47 MX Addict replied:
Hi Teodor,

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

Replied 14 Jun 2016 13:57:44
14 Jun 2016 13:57:44 Teodor Kuduschiev replied:
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.
Replied 14 Jun 2016 19:53:20
14 Jun 2016 19:53:20 MX Addict replied:
Hi Teodor,

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

Replied 14 Jun 2016 20:08:34
14 Jun 2016 20:08:34 Teodor Kuduschiev replied:
Great, that you managed to fix the problem!
Replied 29 Nov 2016 09:31:54
29 Nov 2016 09:31:54 MX Addict replied:
Hi Teodor,

I purchased DMXzone Server Data Formatter as you mentioned earlier.

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.
Replied 29 Nov 2016 10:01:15
29 Nov 2016 10:01:15 Teodor Kuduschiev replied:
I am not sure i understand your last question? What exactly do you mean by:

QuoteHow 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.

Replied 29 Nov 2016 10:53:32
29 Nov 2016 10:53:32 MX Addict replied:

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
Replied 29 Nov 2016 11:01:40
29 Nov 2016 11:01:40 MX Addict replied:
I mail you the link
Replied 30 Nov 2016 08:47:32
30 Nov 2016 08:47:32 Teodor Kuduschiev replied:
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:
Replied 30 Nov 2016 10:29:58
30 Nov 2016 10:29:58 MX Addict replied:
Hi Teodor,

So far this works

Only I had to add data-binding-value="{{datum.formatDate( &quot;dd-MM-yyyy&quot; )}}" 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)

Replied 30 Nov 2016 10:48:56
30 Nov 2016 10:48:56 Teodor Kuduschiev replied:
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 ...
Replied 30 Nov 2016 10:57:43
30 Nov 2016 10:57:43 MX Addict replied:
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?
Replied 30 Nov 2016 11:02:39
30 Nov 2016 11:02:39 Teodor Kuduschiev replied:
Yes it is correct.

Reply to this topic