Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    When is a month a day? (vb.net)

    I have a calendar which when user selects a date, the date is used as part of the response and redirect. e.g. 01/04/2006
    On redirecting to a new page the date part of the response is used as part of an ongoing query.

    When the user selects any date of 1 to 12, then the date is being treated as mm/dd/yyyy
    but when the selection is above 12, then the date is being treated as dd/mm/yyyy

    Note though-the value of the querystring remains as selected i.e. 01/04/2006, but it looks like it's when the string is used in the select query that it is all going awry.

    Should I be switching characters around if the first two characters are less than 12?

    Has anyone a solution to this please?

    TIA
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: When is a month a day? (vb.net)

    I don't understand. Are you intentionally doing this shift or is it accidental. It is risky to assume that any date of 1 to 12 indicates a month rather than, say, April 1, 2006. If you are handling it that way in code, show us the code. On the whole, I would recommend using a calendar of some sort to eliminate the possible confusion. Another method is a dropdown list of months and a separate list of days for that month so the user can specify without any concerns about date format.
    Charlotte

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: When is a month a day? (vb.net)

    You may be falling foul of the old Access/VBA issue where you have to explicitly format a date in US style when building a query string with it. Try assigning the date in US format to a string variable and using that to see if it helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: When is a month a day? (vb.net)

    That is is a SQL one, not specifically Access or VBA, Rory.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When is a month a day? (vb.net)

    As Charlotte has suggested, it would be nice if you could post some code related to your question.

    If I read your question correctly, it sounds like you're getting an unexpected result from a Calendar control. Is this correct? What is the region format of the server? If you're using dd/mm/yyyy format then it's likely that the calendar control will output in this format.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: When is a month a day? (vb.net)

    I am using a calendar control on the page1.
    <pre><asp:calendar id="Calendar1" runat="server" Width="472px" Height="190px" BorderWidth="1px"
    NextPrevFormat="FullMonth"
    BackColor="White" ForeColor="Black" Font-Size="9pt" Font-Names="Verdana"
    BorderColor="White">
    <TodayDayStyle BackColor="#CCCCCC"></TodayDayStyle>
    <NextPrevStyle Font-Size="8pt" Font-Bold="True" ForeColor="#333333"
    VerticalAlign="Bottom"></NextPrevStyle>
    <DayHeaderStyle Font-Size="8pt" Font-Bold="True"></DayHeaderStyle>
    <SelectedDayStyle ForeColor="White" BackColor="#99FF99"></SelectedDayStyle>
    <TitleStyle Font-Size="12pt" Font-Bold="True" BorderWidth="4px"
    ForeColor="#333399" BorderColor="Black"
    BackColor="White"></TitleStyle>
    <OtherMonthDayStyle ForeColor="#999999"></OtherMonthDayStyle>
    </asp:calendar>

    </pre>

    From this the user selects the transaction date.
    When I was seeing the 'error' I used a label to return exactly what was being selected and I saw that the format was stying precisely as dd/mm/yyyy
    <pre>Private Sub Calendar1_SelectionChanged(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles Calendar1.SelectionChanged
    Me.lblDateChoice.Text = Me.Calendar1.SelectedDate
    End Sub</pre>


    Looking into the help area I see:
    <hr>Date values must be enclosed within number signs (#) and be in the format M/d/yyyy, for example #5/31/1993#. If you convert a Date value to the String type, the date is rendered according to the short date format recognized by your computer, and the time is rendered according to the time format (either 12-hour or 24-hour) in effect on your computer.
    <hr>
    I am using the date string from page1 as a query string so reading from the above I see that it should be treating the string as per the date locale - which is short date: 06/04/2006

    So it should be reading the string as dd/mm/yyyy

    Part code from my SELECT is:
    <pre>"and ((#" & strID & "#-tbl_SalesTransactions.TRANSACTION_DATE)>=(#" & strID &
    "#-#31/12/2004#)) "
    </pre>

    where strID is the querystring of 06/04/2006 from page1

    Sorry Mark, yes the results are unexpected as I want the string format to stay as dd/mm/yyy - as that is the format in my base tables.

    TIA
    Alan

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When is a month a day? (vb.net)

    So is your querystring coming across correctly? I'm not clear on what is not working correctly now.

    If a date is not in the correct format, you can always assign the string date to a date variable:
    Dim selectedDate as System.DateTime = DateTime.Parse(Querystring("<ItemName>"))

    You can also output the date into the correct string format:
    Dim dateString as String = selectedDate.ToString("dd/mm/yyyy")

    (You'll want to double-check my string formatting syntax. I don't use it enough to remember the details.)

  8. #8
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: When is a month a day? (vb.net)

    As it stands at present:
    User selects date from calendar: 06/04/2006
    The label text is then set assigned the selected date: 06/04/2006
    The querystring("id") is included as the address: id=06/04/2006

    Whilst viewing the de###### I can see that the string is coming through as 06/04/2006

    So I am now presuming that the error is lying within the SQL operation and the interpretation of the string date as mm/dd/yyyy is happening within the connection.

    Cheers
    Alan

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When is a month a day? (vb.net)

    Don't know whether it'll help, but there's a Date Class available at The Code Project on this page - which may reduce the breadth of the issues. HTH
    Gre

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: When is a month a day? (vb.net)

    You apparently missed the point that SQL requires dates in US format. It doesn't matter what format the date starts out it, it has to be in US format to be processed properly by the SQL engine. If you put it in dd/mm/yyyyy, SQL will still try to interpret it as mm/dd/yyyy because that is what it expects. It isn't an error as such, it just can't handle a date in any other format. Some might think that is horribly US-centric, but it's the way SQL operates. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: When is a month a day? (vb.net)

    Thanks everyone.
    What Charlotte said was spot on, I was looking at it from the UK perspective thinking I was right and mm/dd/yyyy was wrong.

    With that in mind I can move forward with "adjusting" any received strings accordingly and setting them to mm/dd/yyyy - which isn't a problem in itself, it's just nice to know that you are doing it for a valid reason and not just circumventing an error.


    Thanks again everyone

    Alan
    Wet but a veritably balmy 6 degrees in Cheshire

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When is a month a day? (vb.net)

    Be sure to leverage the .NET string formatting to help with the date.

    You can do something like:
    'ukDate is the date from the Calendar control in dd/mm/yyyy format
    Dim usDateString as String = ukDate.ToString("{0:mm/dd/yyyy}")

    Double-check my string formatting syntax - I always have to look that up...

    This should be MUCH easier than converting the date any other way.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: When is a month a day? (vb.net)

    Yes, it's much easier in .Net, but all you need is the format string itself in vb.net: UKDate.ToString("mm/dd/yyyy"). I don't work with ASP, so I can't speak to that.
    Charlotte

Posting Permissions

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