Date format output from Access to ASP

Report · Mar 27, 2010

I see many have had this problem in the past with no real solution that I've found, but I'll try anyway in case I missed something.

Using MS Access, DW CS3, ASP VBScript.

My Access database date field type is Date/Time with an input mask of 00/00/00;0;_ for a format of 03/27/10 which works fine in the db. However, the output to my ASP page shows 03/27/2010 when previewed in a browser. I want the output to match the database - 03/27/10.

My regional settings are correctly set at 03/27/10. The Bindings window in DW offers many date formats which will not bind -- the binding remains "Selected...None" no matter which format is picked. If I change my db date field to text, the dates do not sort in proper order. Any solutions?

Report · Mar 27, 2010

>My Access database date field type

>is Date/Time with an input mask of 00/00/00;0;

The input mask setting is irrelevent. Date/Time fields are internally stored the same regardless of input mask. AFAIK, ASP/VBscript does not support date output with a 2-digit year. You will need to write a custom funtion.

Report · Mar 27, 2010

I had a feeling that would be the answer.... okay, more research! Thanks

Report · Mar 27, 2010

Just use a simple function to convert it:

<%

Function FormatDate(DateToFormat)
FormatDate = (Left (FormatDateTime (DateToFormat,2), InStrRev(FormatDateTime(DateToFormat,2), "/")) _
& Right (FormatDateTime (DateToFormat,2), 2) )
End Function

response.Write FormatDate (Now())

%>

Report · Mar 28, 2010

Thank you! You've basically handed this code to me on a silver platter! Unfortunately, I'm embarrassed at how inexperienced I am in scripting and my attempts at making this work are failing using several combinations, even after researching for hours.

For test purposes, I created an ASP VBScript page with only the date field from my db. The full path of that field is <%=(rs_date.Fields.Item("Date").Value)%>. So I assume I replace (DateToFormat) with my field name and this script goes in the <head> tag with <script type="text/vbscript">?

<%

Function FormatDate(DateToFormat)
FormatDate = (Left (FormatDateTime (DateToFormat,2), InStrRev(FormatDateTime(DateToFormat,2), "/")) _
& Right (FormatDateTime (DateToFormat,2), 2) )
End Function

Response.Write FormatDate (Now())

%>

<%

Function FormatDate(rs_date.Fields.Item("Date"))
FormatDate = (Left (FormatDateTime (rs_date.Fields.Item("Date"),2), InStrRev(FormatDateTime(rs_date.Fields.Item("Date"),2), "/")) _
& Right (FormatDateTime (rs_date.Fields.Item("Date"),2), 2) )
End Function

Response.Write FormatDate (Now())

%>

Report · Mar 28, 2010

I was just using Now() as an example date. Use the function I provided "as is" and call it passing your date as a parameter.

<%

Function FormatDate(DateToFormat)
FormatDate = (Left (FormatDateTime (DateToFormat,2), InStrRev(FormatDateTime(DateToFormat,2), "/")) _
& Right (FormatDateTime (DateToFormat,2), 2) )
End Function

response.Write FormatDate (rs_date.Fields.Item("Date"))

%>

Report · Mar 29, 2010

How do I apply this to a repeat region?

</th></tr>

<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rs_date.MoveNext()
Wend
%>

</table>

Report · Mar 29, 2010

>How do I apply this to a repeat region?

You don't apply it to the region. You simply replace the calls to the database field with a call to the function and pass it the database field as a parm, like I showed you in the example.

Report · Mar 29, 2010

Thanks, I got it! Now, to push the envelope, is there a way to have the script add zeros to single months and days?

03/29/10 instead of 3/29/10

01/01/10 instead of 1/1/10

Report · Mar 30, 2010

Looks like I need to use 'mm/dd/yy' instead of the vbShortDate to create leading zeros in my date format. Can this script be edited to do that? I've tried several variations.

<%
Function FormatDate(DateFormat)
FormatDate = (Left (FormatDateTime (DateFormat,2), InStrRev(FormatDateTime(DateFormat,2), "/")) _
& Right (FormatDateTime (DateFormat,2), 2) )
End Function
%>

Report · Mar 30, 2010

>Looks like I need to use 'mm/dd/yy'

VBScript does not support that date format. If you need the leading zero's, you will need to test each date part and add them manually. You can use the VB DatePart() function to extract the day, month and year. Test the length of the day and month and add the 0 if necessary. Then concat them together again.

Report · Mar 30, 2010

Thank you so much for all your help - you've been a tremendous help to me!

Report · Mar 31, 2010

You're most welcome.

Date format output from Access to ASP

Photos