How to set date retrieved from sql server into datepicker in Classic ASP?

Question

I want to make a form in a system that enable the staff to ask approval for leave from their supervisor. The form contains start date, end date, reason, and name of the supervisors. This system also tends to enable the staff to update the application as long the status still in the process which means their supervisor do not take any action yet whether want to approve or not.

When the staff want to update the application, the same form will be shown, and the reason in the form for update is retrieved from database(SQL server) as what the staff fill in the form during the application. The problem is I can retrieve the start and end date but I can't set the date in datepicker. I want to set the date that retrieved from database as selected date in datepicker.

Can anyone help me ? The image of the application from as shown below.

I'm using Classic ASP and SQL server to develop this web system. This is the code for the application form :

 <form name="form1" method="POST" action="TMS_lookCatatanNext.asp" onSubmit="return checkdata();">
<br>
<table width="400" border="1" cellpadding="2" cellspacing="2" class="Kuning">
  <tr align="center"> 
    <td colspan="2"><strong>PERMOHONAN PENGESAHAN KEHADIRAN
      <%if isdate(KehadiranRS("tarikh"))then%>
      <%=day(KehadiranRS("tarikh")) &"/"& month(KehadiranRS("tarikh")) &"/"& year(KehadiranRS("tarikh"))%> 
      <%end if%>
      </strong></td>
  </tr>
  <tr valign="top"> 
    <td width="23%" align="right"> <strong>Pada :</strong></td>
    <td width="77%" <strong> 
       <input type="date" name="tarikh" id="tarikh"><%=KehadiranRS("tarikh")%>
      </strong></td>
  </tr>
  <tr valign="top"> 
    <td width="23%" align="right"> <strong>Hingga :</strong></td>
    <td width="77%"> <strong> 
      <input type="date" name="hinggaTarikh" id="hinggaTarikh"><%=KehadiranRS("hinggaTarikh")%>
      </strong></td>
  </tr>
  <tr valign="top"> 
    <td width="23%" align="right"> <strong>Catatan :</strong></td>
    <td width="77%"> <strong> 
      <textarea name="alasan" cols="30" rows="6" id="alasan"><%if len(KehadiranRS("alasan"))>0 then%><%=server.HTMLEncode(KehadiranRS("alasan"))%><%end if%></textarea>
      </strong></td>
  </tr>

Answer 1

You should put the value from database as the value of the date picker:

<input type="date" name="tarikh" id="tarikh" value="<%=KehadiranRS("tarikh")%>" />

However, the browser's date picker is very strict about the allowed format, and forcing the format of the value to be YYYY-MM-DD as mentioned in this answer to How to set default value to the input[type="date"].

So in order for the plain HTML picker to work, you have to manipulate the date string on the server side and output the format that is expected. For this, you can use a custom function I made:

Function ChangeDateFormat(ByVal rawDate, ByVal sourceFormat, ByVal targetFormat)
    Const adVarChar = 200  'the SQL datatype is varchar
    Const adInteger = 3  'the SQL datatype is integer
    Const adOpenStatic = 3

    Dim indicesRecordset, numberBuffer, x
    Dim currentChar, newDateFormat
    Dim sourceDay, sourceMonth, sourceYear
    Dim sourceDayPadded, sourceMonthPadded
    Dim formatMapping, formatKey

    'possible mapping values
    Set formatMapping = CreateObject("Scripting.Dictionary")
    formatMapping.Add "DD", 0
    formatMapping.Add "D", 0
    formatMapping.Add "MM", 0
    formatMapping.Add "M", 0
    formatMapping.Add "YYYY", 0
    formatMapping.Add "YY", 0

    'Create a disconnected recordset
    Set indicesRecordset = CreateObject("ADODB.RECORDSET")
    indicesRecordset.Fields.append "Index", adInteger
    indicesRecordset.Fields.append "Format", adVarChar, 1
    indicesRecordset.Fields.append "Value", adInteger
    indicesRecordset.CursorType = adOpenStatic
    indicesRecordset.Open

    'force string
    rawDate = CStr(rawDate)

    'in the source format, d, D, dd, DD etc do not really matter
    sourceFormat = Replace(Replace(Replace(Replace(UCase(sourceFormat), "DD", "D"), "MM", "M"), "YYYY", "Y"), "YY", "Y")
    indicesRecordset.AddNew Array("Index", "Format", "Value"), Array(InStr(sourceFormat, "D"), "D", 0)
    indicesRecordset.AddNew Array("Index", "Format", "Value"), Array(InStr(sourceFormat, "M"), "M", 0)
    indicesRecordset.AddNew Array("Index", "Format", "Value"), Array(InStr(sourceFormat, "Y"), "Y", 0)
    indicesRecordset.Update
    indicesRecordset.Sort = "Index"
    indicesRecordset.MoveFirst
    ChangeDateFormat = rawDate
    If (indicesRecordset.RecordCount = 3) Then
        'extract numbers from the raw date:
        numberBuffer = ""
        For x=1 To Len(rawDate)
            If indicesRecordset.EOF Then
                Exit For
            End If
            currentChar = Mid(rawDate, x, 1)
            If IsNumeric(currentChar) Then
                numberBuffer = numberBuffer & currentChar
            ElseIf Len(numberBuffer)>0 Then
                indicesRecordset("Value") = CInt(numberBuffer)
                indicesRecordset.MoveNext
                numberBuffer = ""
            End If
        Next
        If Not(indicesRecordset.EOF) And Len(numberBuffer)>0 Then
            indicesRecordset("Value") = CInt(numberBuffer)
        End If
        indicesRecordset.Update

        'create desired new format
        targetFormat = UCase(targetFormat)
        newDateFormat = targetFormat
        indicesRecordset.Filter = "Format='D'"
        indicesRecordset.MoveFirst
        sourceDay = CStr(indicesRecordset("Value"))
        indicesRecordset.Filter = "Format='M'"
        indicesRecordset.MoveFirst
        sourceMonth = CStr(indicesRecordset("Value"))
        indicesRecordset.Filter = "Format='Y'"
        indicesRecordset.MoveFirst
        sourceYear = CStr(indicesRecordset("Value"))
        sourceDayPadded = sourceDay
        If Len(sourceDayPadded)<2 Then sourceDayPadded = "0" & sourceDayPadded
        sourceMonthPadded = sourceMonth
        If Len(sourceMonthPadded)<2 Then sourceMonthPadded = "0" & sourceMonthPadded

        formatMapping("DD") = sourceDayPadded
        formatMapping("D") = sourceDay
        formatMapping("MM") = sourceMonthPadded
        formatMapping("M") = sourceMonth
        formatMapping("YYYY") = sourceYear
        formatMapping("YY") = Right(sourceYear, 2)

        For Each formatKey In formatMapping.Keys
            newDateFormat = Replace(newDateFormat, formatKey, formatMapping(formatKey))
        Next
        ChangeDateFormat = newDateFormat
    End If
    Set formatMapping = Nothing
    indicesRecordset.Close
End Function

I know it looks complicated, but using it is trivial, e.g. in your case:

<input type="date" name="tarikh" id="tarikh" value="<%=ChangeDateFormat(KehadiranRS("tarikh"), "MM/DD/yyyy", "YYYY-MM-DD")%>" />