www.webdeveloper.com
Results 1 to 5 of 5

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

  1. #1
    Join Date
    Jun 2006
    Posts
    30

    Retrieve Records: Some UK date format, some US

    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!

  2. #2
    Join Date
    Feb 2003
    Posts
    2,745
    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...

  3. #3
    Join Date
    Jun 2006
    Posts
    30

    Smile

    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

  4. #4
    Join Date
    May 2006
    Location
    Sydney, Australia
    Posts
    125
    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.

  5. #5
    Join Date
    Feb 2003
    Posts
    2,745
    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 Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles