[RESOLVED] Forcing custom date passed Regional Settings

[HawkeyeD]

Hi everyone,

This has been driving me batty for the last while. I have an application that is passing up data to a SQL server table. Part of the data is dates. Because of settings on the SQL server the update will not accept the MM/dd/yyyy format. I have been trying for quite some time to get around this.

For some more information. Changing the update method or the server regional settings is not an option and I don't want to change my regional settings as this may not reflect the production server when this goes live.

Here is what I have tried so far

        Dim dateEnd As Date = Date.MinValue
        Dim dateStart As Date = Date.MinValue

        If IsDate(Me.txtPostEnd.Text) Then
            dateEnd = CType(Me.txtPostEnd.Text, Date)
            dateEnd = dateEnd.ToString("dd/MM/yyyy")
            dbJobAppData.EndDate = dateEnd.ToShortDateString
       End If

I hae also tried:

        If IsDate(Me.txtPostEnd.Text) Then
            dateEnd = CType(Me.txtPostEnd.Text, Date)
            dbJobAppData.EndDate = CDate(dateEnd.ToString("dd/MM/yyyy"))
        End If

When run I receive error that conversion of string '28/02/2011' to type Date is not valid.

It works fine if the day is 12 or below because it still thinks that is the month. Is there a way to force the code to recogize that I want to use dd/MM/yyyy format or even dd/MMM/yyyy format?

This has to be done at thhe vb code side as the update to the SQL server is completed via a custom framework so I can't customize the SQL code to insert the data using a CONVERT

Thanks for any suggestions.

[dglienna]

Have you tried searching MSDN?

http://msdn.microsoft.com/en-us/libr...=VS.80%29.aspx

[HawkeyeD]

Thanks for the reply.

Won't this affect the entire form (or in my case the entire asp page)? Are dates controlled by the localization; meaning that North American localization will not allow me to use the format I wish to use?

[dglienna]

If you detect the current settings, you can modify the query to fit the locale

[HawkeyeD]

Ok, so I am either doing something completely wrong or misunderstood your post (probably both).

I have tried using the culture settings in localizing my data as follows:

        If IsDate(Me.txtPostEnd.Text) Then
            dateEnd = CType(Me.txtPostEnd.Text, Date).ToString("dd/MM/yyyy", CultureInfo.InvariantCulture)
            MsgBox(dateEnd.ToString("dd/MM/yyyy", CultureInfo.CreateSpecificCulture("en-US")))
            dateEnd = dateEnd.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture)
            dbJobAppData.EndDate = dateEnd.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture)

        End If

I tested the data using a msgbox and am getting the correct result of 28/02/2011 however when the value is assigned to the variable DateEnd and dbJobAppData.EndDate the value is still resolving to 02/28/2011.

Am I doing something wrong here. Am I missing something entirely.

I even tried dateEnd = dateEnd.toshortDate (my system's shortDate is set to dd/MM/yyyy.

I am really confused why the format is not holding on variable assigment.

Thanks for your continued help

[dglienna]

Download the samples and try out their programs

[HawkeyeD]

I'm beginning to wonder if there isn't something else going on. I used the samples as you suggested, and the dates were coming in fine. I expected this as all the dates were being passed to a text box and I was able to recreate the correct format using a similar method, however when I put break points on the samples and ran it when at the breakpoing the date variables all had a value of 2/14/2011, but were showing up as 14/02/2011 in the form view.

I then broke down and changed all of my regional settings on my system to dd/MM/yyyy. Reran the samples code and received the same results. It will display to text the format I want, but when passing values within the code it still passes using the format MM/d/yyyy

Is there any settings in Visual Studio 2010 that can control this, or is it strictly controlled by the regional settings?

Do you happen to have any other suggestions?


Thanks again

[dglienna]

The DB engine has regional settings for the DB.

[HawkeyeD]

Ok, so excuse me for a second while I remove the egg from my face. Turns out the error while correct was for a different field I was passing up. Once corrected all others worked fine and the formatting was not necassary.


Thanks again for your help.