Retrieve Records: Some UK date format, some US

**devine** · 06-01-2006 05:42 AM

I have an ASP page, which will display records for a selected date.

It will display my records for selected dates 13 March 06 and 16
March 06, but will not return any records when 10 April 06 or 10 May
06 have been selected (Test database contains records for 13/06/06,
16/03/06, 10/04/06, 10/05/06 UK date format)
Is it possible that it can recognise days 13 & 16 as they can't be
confused with a month number, but thinks that 10th April is in fact
4th October.....actually I have just tested this after writing it
and this does seem to be the case!)

Is there anyway to correct this?
Strangely enough, the address bar is showing:
http://127.0.0.1/schedule2.asp?day=4&month=10&year=2006, but after
selecting 4th October, to display the record from 10th April.
But it processes 13th March correctly, with address bar as:
http://127.0.0.1/schedule2.asp?day=13&month=3&year=2006

Code is as follows:

Code:

<%@Language=vbscript LCID=2057%>
<!-- #Include file = "ADOVBS.INC" -->
<%
datToday = Date()
showDay = Request.QueryString("day")
If showDay = 0 Then
showDay = Day(datToday)
End If
showMonth = Request.QueryString("month")
If showMonth = 0 Then
showMonth = Month(datToday)
End If
showYear = Request.QueryString("year")
If showYear = 0 Then
showYear = Year(datToday)
End If
searchDate = showDay & "/" & showMonth & "/" & showYear

' Depending on your server configuration...usually if you're in
Europe...day and month may
have to be reversed.
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/inetpub/wwwroot/change control.mdb"
set rs=Server.CreateObject("ADODB.recordset")
' Set SQL statement
strSQL = ("SELECT * FROM tblform WHERE tblform.DATE=#" & showDay
& "/" & showMonth & "/" &
showYear & "# ORDER BY START ASC" )

' Open Recordset Object
rs.Open strSQL,conn
%>
<HTML>
<HEAD><TITLE>CCF Schedule</TITLE></HEAD>

<BODY>
<CENTER>
<H2>
Date shown: <% = showDay & " " & MonthName(showMonth) & " " &
showYear%>
</H2>
Select Another Date:
<FORM NAME="show_date">
<SELECT NAME="day">
<% For i = 1 to 31 %>
<% If i = Day(datToday) Then %>
<OPTION VALUE="<% = i %>" SELECTED><% = i %>
<% Else %>
<OPTION VALUE="<% = i %>"><% = i %>
<% End If %>
<% Next %>
</SELECT>
<SELECT NAME="month">
<% For i = 1 to 12 %>
<% If i = Month(datToday) Then %>
<OPTION VALUE="<% = i %>" SELECTED><% = MonthName(i) %>
<% Else %>
<OPTION VALUE="<% = i %>"><% = MonthName(i) %>
<% End If %>
<% Next %>
</SELECT>
<SELECT NAME="year">
<% For i = Year(datToday) to Year(datToday) + 5 %>
<% If i = Year(datToday) Then %>
<OPTION VALUE="<% = i %>" SELECTED><% = i %>
<% Else %>
<OPTION VALUE="<% = i %>"><% = i %>
<% End If %>
<% Next %>
</SELECT>
<INPUT TYPE=submit VALUE="Select">
</FORM>

<%

  ' Build a table here
  Response.Write("<TABLE CELLSPACING=2 CELLPADDING=0 COLS=3 WIDTH=800
BGCOLOR=#F0F0FF>")
  Response.Write("<TR>")
  Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15%
BGCOLOR=#000080><B>CCF</B></TD>")
  Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15%
BGCOLOR=#000080><B>Type</B></TD>")
  Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15%
BGCOLOR=#000080><B>Date</B></TD>")
  Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15%
BGCOLOR=#000080><B>Start</B></TD>")
  Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=65%
BGCOLOR=#000080><B>Summary</B></TD>")
  Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15%
BGCOLOR=#000080><B>Risk</B></TD>")
  Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=20%
BGCOLOR=#000080><B>Area</B></TD>")
  Response.Write("</TR>")

  Do While Not rs.EOF
   Response.Write("<TR>")
   Response.Write("<TD><a href=""choose.asp?qryid=" &RS("CCF")
& """>" & RS("CCF") &
"</a></TD>")
   Response.Write("<TD>" & rs("Type") & "</TD>")
   Response.Write("<TD>" & rs("Date") & "</TD>")
   Response.Write("<TD>" & rs("Start") & "</TD>")
   Response.Write("<TD>" & rs("Summary") & "</TD>")
   Response.Write("<TD>" & rs("Risks") & "</TD>")
   Response.Write("<TD>" & rs("Area") & "</TD>")
   Response.Write("</TR>")
   rs.MoveNext
  Loop

  rs.Close

  Response.Write("</TABLE>")


Set rs=Nothing
conn.Close
Set conn=Nothing
%>

Thanks in advance for any help you may be able to provide!

**russell** · 06-01-2006 03:53 PM

are you saying that the dates are STORED in both formats? not possible if you use the correct data type. That said, your query sure looks like MS Access thinks it's a date. If the issue is input -- inserting dates the user enters -- then (1) using select elements or a DHTML date control instead of letting users key in dates directly, and (2) doing input validation before sending to the database will resolve this. Also, look at the DateFormat() function.

Access should follow the same date format that is set on the server

Probably I don't fully understand your question...

**devine** · 06-02-2006 12:38 AM

Hi Russell,

Many thanks for your response.

My database is MS Access and the data-type is Date/Time. Format is short
date (i.e.19/06/1994).

The test database contains 4 different dates, all in UK date format:

13/03/2006
16/03/2006
10/04/2006
10/05/2006

In my ASP page, when I select 13th March or 16th March, my 2 records are retrieved correctly.
When I select 10th April or 10th May, no records are retrieved, but if I select 4th October, the record for 10th April is retrieved!

So it seems that days 13th & 16th can be retrieved correctly, as there are no months 13th or 16th, but day 10th can be confused with month 10!
Not sure how to correct this.

If I change my existing coding in ASP to transpose day and month

Code:

strSQL = ("SELECT * FROM tblform WHERE tblform.DATE=#" & showDay
& "/" & showMonth & "/" &
showYear & "# ORDER BY START ASC" )

to

Code:

strSQL = ("SELECT * FROM tblform WHERE tblform.DATE=#" & showMonth
& "/" & showDay & "/" &
showYear & "# ORDER BY START ASC" )

I'm sure this will confuse the issue more! For example it will probaby process 2nd June 2006 as 6th February 2006.
I will try transposing day and month in the ASP code as a test, but if you have any ideas as to why this is happening or how to resolve, please do let me know!
Thanks

**vanny** · 06-02-2006 01:16 AM

I have been down this path and havent found a good method of retrieving dates, I recommend that when storing the dates pass them through in a format that the computer cant confuse.

Two things you can look at is putting in your ASP are:

1 - Session.LCID

Which i didnt find very effective

2 - Format your date better

I find by passing dates as 02-MAR-2006 that it always returns 02/03/2006, and never 03/02/2006

I have some scripts I use to format dates to make sure they go in and out correctly, it is a real pain and a pity that there is not a universal format.

The other reason the two of your dates format correctly is as the computer recognises that there is no 16th or 13th month so therefore it must be a day.

Hope that helps.

**russell** · 06-02-2006 11:28 AM

So the Access DB stores the date as dd/mm/yyyy and IIS uses mm/dd/yyyy.

This is a hosted site I take it? Probably can't get the host to change the localization settings, so you need to either change the format MS Access is storing dates as, or as Vanny said, pass you dates like this '02-MAR-2006' which Access will properly format to your setting.

Thread: Retrieve Records: Some UK date format, some US

Thread Tools

Display

Retrieve Records: Some UK date format, some US

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions