Best practice for working with DateTime in and sql server

My web app displays controls in uk , indian format ie dd/mm/yyyy
but I got lots of error when inserting this format to database like String was not recognized as a valid DateTime.
or unable to convert varchar type to datetime.
So finally here is the Best practice for working with DateTime in and sql server.

Make an entry in your web.config file like this

  (!-- To let the default date as en-US style mm/dd/yyyy in backgrount and let controls have en-GB dd/mm/yyyy format /  Culture fr-CH, Date: 30.11.2007 00:00:00-->
    (globalization   requestEncoding="utf-8"   responseEncoding="utf-8"   culture = "en-US"   uiCulture = "en-US" />

Let the user see dates in dd/mm/yyyy format

(asp:TextBox ID="txtHolidate" runat="server" CssClass= "tbox" Font-Names="Arial" 
                  ForeColor="#0066FF" Wrap="False" Height="16px" Width="94px" 
             ( asp:CalendarExtender ID="CalendarExtender2" runat="server" 
                  Enabled="True" TargetControlID="txtHolidate" Format="dd/MM/yyyy" >

But you workaround in MM/dd/yyyy format in your code and sql query like

  Dim ukProvider As IFormatProvider = New System.Globalization.CultureInfo("en-GB")  ' uk style date dd/MM/yyyy
        Dim holidate As Date
        If txtHolidate.Text.Trim <> "" Then
            holidate = DateTime.Parse(txtHolidate.Text, ukProvider, System.Globalization.DateTimeStyles.NoCurrentDateDefault) '' it will convert dd/mm/yyyy to mm/dd/yyyy
            lblError2.Text = "Error: Enter a valid date in dd/MM/yyyy format. "
            Exit Sub
        End If

Also Change your Date time setting to English - United states from Control Panel > Regional Settings
It will effect your sql server date time format. 

By: Vinod Kotiya


Most Viewed Post

solution for facebook waiting

Connect Sony XPLOD MEX-BT2500 Bluetooth CD Receiver with ur mobile

Crystal Report : Show data horizontally (Left to right) i.e. columns as rows

Google+ Followers