VB6 Using U.S. and International Dates in Text Boxes, Data Grid and storing in SQL Server

I have a VB form that displays two text boxes: one defaults to today's date, txtCommentDate, and the other is blank for the user to enter some activity/comment associated with that date, txtComment. They can change the date in txtCommentDate if needed, but it defaults to today's date.  They can then click the AddComment button that updates this record to a relational table in SQL server, and re-displays this as a new record in the Comments data grid back on the form.

All worked fine when it was just U.S. users, but I now have Australia and I am having trouble displaying it differently for AUS, for example dd/mm/yy and then getting it into the database as the right date.  When I pass this formatted date to SQL, seems no matter what I do it puts it in incorrectly.  For example, if the date is displayed as today of 17/10/11, it goes into SQL as 10/11/2017.  I've tried re-formatting it as "mm/dd/yy" prior to passing to the SQL call, but this does nothing.

My questions are:
--I want to continue to display it one way for U.S. in the txtCommentDate text box and the other way for Australia.  How best to do this?
--When they click AddComment button, I need to pass the value of the txtCommentDate box into SQL Server as the valid date.  How do I do this?
--Since the DataGrid has its own properties, how best to display the date as U.S. or AUS in the Data Grid's display of this newly added CommentDate and Comment record.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
>> I've tried re-formatting it as "mm/dd/yy" prior to passing to the SQL call, but this does nothing.<<
When you format it for the database, always, always use an unambiguous date format such as YYYYMMDD.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VDannerAuthor Commented:
Thank you.  This got me on the right track.  With your suggestion in mind, I changed the format/display of the date to "mm-dd-yyyy" for the U.S. and "dd-mm-yyyy" for Australia.  Then at the time I wanted to insert it into the database, I applied the following:
Dim strDate As String
strDate = Format(txtCommentDate.Text, "yyyy-mm-dd")
'Call the method to update the comment record
lngRC = mobjclsDirects.InsertComment(txtAccountID.Text, CDate(strDate), txtComment.Text, strMsg(), gobjConn, mobjcmd)

This passed it to the class function InsertComment that accepts it as a Date, and inserts it into SQL as a DBTimeStamp.

That may not be exactly what you had in mind, but it worked.  Thanks to your suggestion for getting me on the right track.
Anthony PerkinsCommented:
>>That may not be exactly what you had in mind, but it worked.<<
Actually, that is pretty close to what I had in mind.  I would have used Stored Procedures, but the outcome is the same.
VDannerAuthor Commented:
Good I'm glad to read that this is pretty close to your approach.  I do often use Stored Procedures - just didn't in this case (older code).  Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.