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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

I am using a calendar control on the page1.

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

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

Looking into the help area I see:
[indent]

---

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.

---

[/indent]
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:

"and ((#" & strID & "#-tbl_SalesTransactions.TRANSACTION_DATE)>=(#" & strID & 
"#-#31/12/2004#)) "

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

Reply | Quote

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(“”))

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.)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote