Inserting and Diplaying the Regional Date Format correctly with Dreamweaver

Many people have trouble converting, inserting, updating and diplaying the correct regional date format when using databases like MS-Access. This tutorial teaches you how to insert and display the correct regional date format correctly with Dreamweaver.

If your website is hosted on a US server or a server with US regional settings, you might have some trouble to get Dreamweaver to insert and display the correct regional date format if you are using a database like MS-Access in conjunction with SQL. While the US date format is MM-DD-YY, in my country, The Netherlands, the date format is DD-MM-YY. The problem is that MS-Access only accepts US-format dates, and SQL cannot be told what regional dateformat is used. So, how can we orchestrate Dreamweaver to format, insert and display regional dates correctly? The solution is quite simple, actually.

First, read this excellent article on the ISO date-format by Giuliano Sauro:
http://www.4guysfromrolla.com/webtech/tips/t022202-1.shtml

It teaches you how the ISO date format can be used to convert a date to the ISO date-format (YYYY-MM-DD). This is a non-ambiguous date-format that SQL and MS-Access handle correctly, according to the Locale Identifier. (LCID)

Then, insert these two pieces of code in the head of your asp-page:

 ' Set your LCID here. 
      For instance, '1033' is US-English regional setttings (default)

      <% Session.LCID = 1043 'Dutch regional setttings 
      %>

<%
Function IsoDate(dteDate)
'Version 1.0 by Giuliano Sauro
   If IsDate(dteDate) = True Then
      DIM dteDay, dteMonth, dteYear
      dteDay = Day(dteDate)
      dteMonth = Month(dteDate)
      dteYear   = Year(dteDate)
      IsoDate = dteYear & _
         "-" & Right(Cstr(dteMonth + 100),2) & _
         "-" & Right(Cstr(dteDay + 100),2)
   Else
      IsoDate = Null
   End If
End Function
%>

And finally, find this piece of standard Dreamweaver insert/update form-record code, and insert the red code.


      
 MM_fieldsStr  = "order|value|number|value|orderdate|value"
 MM_columnsStr = "order|',none,''|number|',none,''|orderdate|#,none,NULL"

  ' create the MM_fields and MM_columns arrays
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")
  
  ' set the form values
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
'--convert date to ISO date-format
	If 	IsDate(MM_fields(MM_i+1)) Then 
		MM_fields(MM_i+1) = IsoDate(MM_fields(MM_i+1))
	End If
'--
  Next

Et voila, it works. The date will be inserted and diplayed according to your LCID setting. The only flaw is that the inserted code checks and converts all form fields for data that can possibly converted to a date. Quite simple!

Comments

Iso Date.

March 26, 2004 by Arjan Ruter

Nice code to convert the date to IsoDate. It solves my problem, but I also use a price field in my form and after the Update record behaviour, the price has changed to a number I don't want. Is it difficult to skip certain form fields?

Arjan

problem with time insert

April 2, 2004 by jack vermeulen

Great code but i have a insert field for inserting a time. The time isnt put in the database and gives as result 0:00:00.

I hope you got a solution for that.

Thanks,

Jack

Great Code

October 10, 2005 by Carlos Leal

Thanks, it help me a lot...

Inserting Time

January 27, 2006 by Tom Theys

I want to insert and edit day, month, year and time.I tried this but it gives the message: Wrong number of arguments or invalid property assignment: 'Time'

<%
Function IsoDate(dteDate)
'Version 1.0 by Giuliano Sauro
   If IsDate(dteDate) = True Then
      DIM dteDay, dteMonth, dteYear, dteTime
      dteDay = Day(dteDate)
      dteMonth = Month(dteDate)
      dteYear   = Year(dteDate)
   dteTime = Time(dteDate)
      IsoDate = dteYear & _
         "-" & Right(Cstr(dteMonth + 100),2) & _
         "-" & Right(Cstr(dteDay + 100),2) & _
   " " & dteTime
   Else
      IsoDate = Null
   End If
End Function
%>

See all 5 Comments

You must me logged in to write a comment.