[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 715
  • Last Modified:

SQL Date Format ASP.NET

Hi,
    I have the following ASP.NET page on a US server displaying dates in UK format. How can I change it so Inserts & Updates work correctly with the date in UK format?

Thanks Paul.

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
            AutoGenerateColumns="False" CellPadding="4" DataKeyNames="DispensationID" DataSourceID="SqlDataSource1"
            ForeColor="#333333" GridLines="None">
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="DispensationID" HeaderText="DispensationID" InsertVisible="False"
                    ReadOnly="True" SortExpression="DispensationID" />
                <asp:BoundField DataField="DispensationNumber" HeaderText="DispensationNumber" SortExpression="DispensationNumber" />
                <asp:BoundField DataField="ReceivedDate" HeaderText="ReceivedDate" SortExpression="ReceivedDate" />
            </Columns>
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#999999" />
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSQLServer %>"
            DeleteCommand="DELETE FROM [Dispensations] WHERE [DispensationID] = @DispensationID"
            InsertCommand="INSERT INTO [Dispensations] ([DispensationNumber], [ReceivedDate] VALUES (@DispensationNumber, @ReceivedDate)"
            SelectCommand="SELECT [DispensationID], [DispensationNumber], CONVERT(varchar(20), [RECEIVEDDATE], 103) AS [RECEIVEDDATE] FROM DISPENSATIONS"
            UpdateCommand="UPDATE [Dispensations] SET [DispensationNumber] = @DispensationNumber, [RECEIVEDDATE] = @ReceivedDate WHERE [DispensationID] =

@DispensationID">
            <DeleteParameters>
                <asp:Parameter Name="DispensationID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="DispensationNumber" Type="String" />
                <asp:Parameter DbType="DateTime" Name="ReceivedDate" />
                <asp:Parameter Name="DispensationID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="DispensationNumber" Type="String" />
                <asp:Parameter DbType="DateTime" Name="ReceivedDate" />
            </InsertParameters>
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>


0
pr_wainwright
Asked:
pr_wainwright
  • 9
  • 8
1 Solution
 
BrandonGalderisiCommented:
Your parameters are datetime into your database so they will insert just fine.  The problem comes with converting datetime values to strings and back.
0
 
senadCommented:
dates display are more or less dependent on regional settings ...
insertion is dependent on database settings i.e what is the datetime field
settings (long,short etc date formats)..
Short datetime is usually accepted on all machines..
0
 
pr_wainwrightAuthor Commented:
BrandonGalderisi,
                             If I over type the date in US format the update works fine. If I leave the date in UK format I get a 'String was not recognized as a valid DateTime.' error. I need to insert code like 'CONVERT(varchar(20), [RECEIVEDDATE], 103) AS [RECEIVEDDATE]' into the Update & Insert SQL statements.

Thanks Paul.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BrandonGalderisiCommented:
The converting TO AND FROM UK and US respectively should be handled by your application, NOT by the database.
0
 
pr_wainwrightAuthor Commented:
Thanks for the replies. I use the code below to format the date in a GridView.

SelectCommand="SELECT [DateTestID], CONVERT(varchar(20), [DateTime], 103) AS [DateTime], [Comment] FROM [DateTest]

I am looking for a way to modify my Insert & Update commands similar to the above so I can enter a date in UK format in my GridView & the Insert & Update commands will work correctly.

InsertCommand="INSERT INTO [DateTest] ([DateTime], [Comment]) VALUES (@DateTime, @Comment)"

UpdateCommand="UPDATE [DateTest] SET [DATETIME] = @DateTime, [Comment] = @Comment WHERE [DateTestID] = @DateTestID">

The below works in SQL Management Studio (database in on a US server) but I don't know how to incorporate it into my above Insert and Update commands which use parameters.

INSERT INTO [DateTest] ([DateTime], [Comment])

VALUES (CONVERT(datetime, '15/04/2009',103), 'My Comment')

Thanks

Paul
0
 
BrandonGalderisiCommented:
You should not be doing it that way.  You should have your application handle the formatting, not SQL Server.  Your inserts, updates and selects should do nothing other than straight datetime values with no formatting.
0
 
pr_wainwrightAuthor Commented:
BrandonGalderisi:
                             Thanks for the reply. How do I create a GridView to Display/Insert/Update in UK format when the database is on a US server? Any advice appreciated.

Thanks
Paul.
0
 
BrandonGalderisiCommented:
Being a SQL Developer, I can't answer that.  I'm just giving you the best practices speech from the SQL Server side in that you want to leave formatting to your application.

You do have this posted in a .Net zone, but seeing as how here is a fair amount of comments already, you may not get a response at this point.  I would suggest either using "Request Attention" or opening a question in a specific .Net zone.

Could I give you the SQL to make it work, sure.  But it really should not be done that way.
0
 
pr_wainwrightAuthor Commented:
BrandonGalderisi:
                             I would appreciate the SQL to make it work. Once I have a quick fix I can work on a better solution.

Thanks
Paul.
0
 
BrandonGalderisiCommented:
You need to change your declaration of @datetime to be of type varchar and not datetime for this to work.

InsertCommand="INSERT INTO [DateTest] ([DateTime], [Comment]) VALUES (CONVERT(datetime, @datetime,103), @Comment)"

UpdateCommand="UPDATE [DateTest] SET [DATETIME] = CONVERT(datetime, @datetime,103), [Comment] = @Comment WHERE [DateTestID] = @DateTestID">
0
 
pr_wainwrightAuthor Commented:
BrandonGalderisi:
                            When I click Edit on the GridView the date is displayed in US format. When I change the date to a UK format & click Update I get a 'String was not recognized as a valid DateTime.' error.

ASPX Page as below. Thanks Paul.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DateTestNew.aspx.cs" Inherits="DateTestNew" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
            AutoGenerateColumns="False" CellPadding="4" DataKeyNames="DateTestID" DataSourceID="SqlDataSource1"
            ForeColor="#333333" GridLines="None">
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <Columns>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" />
                <asp:BoundField DataField="DateTestID" HeaderText="DateTestID" InsertVisible="False"
                    ReadOnly="True" SortExpression="DateTestID" />
                <asp:BoundField DataField="DateField" HeaderText="DateField" SortExpression="DateField" htmlEncode="False"

DataFormatString="{0:dd/MM/yyyy}"/>
                <asp:BoundField DataField="CommentField" HeaderText="CommentField" SortExpression="CommentField" />
            </Columns>
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSQLServer %>"
            DeleteCommand="DELETE FROM [DateTestNew] WHERE [DateTestID] = @DateTestID" InsertCommand="INSERT INTO [DateTestNew] ([DateField], [CommentField])

VALUES (CONVERT(datetime, @DateField,103), @CommentField)"
            SelectCommand="SELECT [DateTestID], [DateField], [CommentField] FROM [DateTestNew]"
            UpdateCommand="UPDATE [DateTestNew] SET [DateField] = CONVERT(datetime, @DateField, 103), [CommentField] = @CommentField WHERE [DateTestID] =

@DateTestID">
            <DeleteParameters>
                <asp:Parameter Name="DateTestID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter DbType="DateTime" Name="DateField" />
                <asp:Parameter Name="CommentField" Type="String" />
                <asp:Parameter Name="DateTestID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter DbType="DateTime" Name="DateField" />
                <asp:Parameter Name="CommentField" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>
   
    </div>
    </form>
</body>
</html>

0
 
BrandonGalderisiCommented:
I'm not a .Net developer.  But like I said, you need to change the field type from datetime to varchar in order for my solution to work.
0
 
pr_wainwrightAuthor Commented:
BrandonGalderisi:
                            I tried replacing datetime with varchar(20) but I still get an error when I input the date in UK format & click Update.

Regards
Paul.
0
 
BrandonGalderisiCommented:
You're going to give a little more than "an error".
0
 
pr_wainwrightAuthor Commented:
BrandonGalderisi:

String was not recognized as a valid DateTime. Error after Changing date to UK format & clicking Update.

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. The statement has been terminated. Error if I leave date in US date format & click Update.

Regards
Paul.

0
 
pr_wainwrightAuthor Commented:
BrandonGalderisi:
                             Finally got it to work. Update command changed to as below.

UpdateCommand="UPDATE [DateTestNew] SET [DateField] = convert(datetime,@DateField,103), [CommentField] = @CommentField WHERE [DateTestID] = @DateTestID"

Needed to change parameter type from
<asp:Parameter DbType="DateTime" Name="DateField" />

to
<asp:Parameter Type="String" Name="DateField" />

Thanks
Paul.
                 
0
 
pr_wainwrightAuthor Commented:
CONVERT(datetime, @datetime,103) did the trick.
0
 
BrandonGalderisiCommented:
Yup.  that's what I said to do! :)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now