SQL Parameter formatting DateTime from UK to US format

Discussion in 'Site Programming, Development and Design' started by srcmatrix, Mar 18, 2011.

  1. I have a problem, my local "Regional Settings" have a date format of DD/MM/YYYY and the LIVE Winhost is using US format MM/DD/YYYY. Hence the page works locally. BUt when user on the live system enter DD/MM/YYYY in a textbox the Winhost SQL SERVER is expecting MM/DD/YYYY

    I been looking around the internet. Cannot find the answer.

    In my .aspx page I am using <%#Bind ("fldDatePaid","0:{dd/mm/yyyy}" %> this allows me to display the date in the UK format no matter what format it is in the database. But when I edit the textbox to change the date. My SQL Statement parameter @fldDatePaid sends the exact text i.e. DD/MM/YYYY to the server and the server is expecting MM/DD/YYYY format.

    ERROR MESSAGE is displayed.

    Has anyone got any tips on how to correct this is convert the SQP parameter

    Thanks in advance for any help....
     
    Last edited by a moderator: Oct 14, 2015
  2. Ray

    Ray

  3. Thanks for the link, I think the problem is when I change the text in the text box.

    Here is my texbox

    <asp:TextBox ID="txtEDatePaid" runat="server" BackColor="#FFFF99" CssClass="DataTextDateEdit"
    Font-Bold="True" ReadOnly="False"
    Text='<%# Bind("fldDatePaid","{0:dd/MM/yyyy}") %>'> </asp:TextBox>

    I am using EditItemTemplate and the page brings the date from the server and displays it in UK format....cool all OK.

    If I click save it will send the date back to the server ALL OK. Probably using this parameter

    <asp:parameter Name="original_fldDatePaid" DbType="Date" />


    If I change the date using dd/MM/yyyy format when code hits my SQL statement it uses this parameter

    <asp:parameter Name="fldDatePaid" DbType="Date" />

    I have enter a date for example 25/03/2011 the page crashes displaying the follwoing error message

    String was not recognized as a valid DateTime.

    I think I need to convert the new fldDatePaid value from dd/MM/yyyy to MM/dd/YYYY

    but I have not got a clue how to do it.

    Any ideas???? Spending hours and hours learning all about date formats but not converting parameters in SQL statement. Please HELP!
     
  4. Ray

    Ray

    What would happen if you simply change the format from...

    Bind("fldDatePaid","{0:dd/MM/yyyy}")

    ..to

    Bind("fldDatePaid","{0:M/dd/yyyy}")
     
  5. :)

    I have tried this. When I load the page the ItemTemplate which is NOT editable is load up and displays date dd/MM/yyyy (25/03/2011)

    I click edit to load the EditItemTemplate

    If the textbox is Text='<%# Bind("fldDatePaid","{0:dd/MM/yyyy}") %>'
    It displays 25/03/2011

    If the texbox is Text='<%# Bind("fldDatePaid","{0:MM/dd/yyyy}") %>'
    It displays 03/25/2011 (US Date format) This is not what I need.

    If the user types in the date 28/03/2011 the save will fail as there are not 28 months.

    I could put caption next to the texbox advising of the format required i.e. MM/dd/yyyy but this is not really the solution.

    Or use a validationgroup but again not the best solution and in my application I plan on using lots of dates so I need to get a fix on this.

    I need to trap whatever format the user enters and convert it to MM/dd/yyyy

    For instance if the user enters

    2011/03/14 - This works as this is the format for SQL SERVER
    03/14/2011 - This works as it is the same regional settings as Winhost server
    14/03/2011 - This fails as the strings is not recognised.
     
    Last edited by a moderator: Oct 14, 2015
  6. Ray

    Ray

  7. Its works

    Dear All,

    I have been all around the world checking out samples and code and all kinds of stuff. 10 hours in total but I have indeed learned a lot about DateTime Formatting.

    Here is one solution and seems to be working ALL OK on the Winhost server. I have put 2 x textboxes on the EditItemTemplate 1 x is visible and uses EVAL (1 way binding READ ONLY) to display the date from the SQL DB. See below. I have used Autopostback to fire the onBlur event sub routine called "checkDate" this fires the code at the bottom.

    The other textbox is NOT visible and has a BIND 2 way binding method. When the sub routine fires it will get the EVAL date run it through the sub and then update the Bound textbox with the US format.

    ******************************************************

    <asp:TextBox ID="txtEDatePaid" runat="server" CssClass="DataTextDateEdit"
    Font-Bold="True" Text='<%# Eval("fldDatePaid", "{0:dd/MM/yyyy}") %>'
    CausesValidation="True" onBlur="checkDate" AutoPostBack="True"></asp:TextBox>

    ******************************************************
    <asp:TextBox ID="txtEDatePaidUpdate" runat="server" CssClass="DataTextDateEdit" Text='<%# Bind("fldDatePaid") %>' Visible="False"></asp:TextBox>

    *******************************************************
    Sub checkDate(ByVal sender As Object, ByVal e As System.EventArgs)

    Dim txtEDatePaid As TextBox
    Dim txtEDatePaidUpdate As TextBox
    Dim strDatePaid As String

    txtEDatePaid = frmInvoiceDetails.FindControl("txtEDatePaid")
    txtEDatePaidUpdate = frmInvoiceDetails.FindControl("txtEDatePaidUpdate")

    If txtEDatePaid.Text = "" Or IsNothing(txtEDatePaid.Text) Then
    txtEDatePaidUpdate.Text = ""

    Else
    strDatePaid = DateTime.Parse(txtEDatePaid.Text, System.Globalization.CultureInfo.GetCultureInfo("en-gb"))
    txtEDatePaidUpdate.Text = strDatePaid
    End If

    End Sub

    *******************************************************
    I spent a lot of time on this and I am sure other uses in this forum maybe having similar problem with DateTime formats so I thought I would post.

    Ray, thanks for your help on this...........

    If anyone knows how to do the same thing on the client-side maybe using JScript I would be very interested as small conversion like this ideally should be done client-side.

    Thanks again. :)
     
    Last edited by a moderator: Oct 14, 2015
  8. Ray

    Ray

    Thanks for the follow up post.
     

Share This Page