asked on

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>

BrandonGalderisi

Your parameters are datetime into your database so they will insert just fine. The problem comes with converting datetime values to strings and back.

senad

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..

pr_wainwright

ASKER

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.

BrandonGalderisi

The converting TO AND FROM UK and US respectively should be handled by your application, NOT by the database.

pr_wainwright

ASKER

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

BrandonGalderisi

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.

pr_wainwright

ASKER

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.

BrandonGalderisi

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.

pr_wainwright

ASKER

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.

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pr_wainwright

ASKER

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>

BrandonGalderisi

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.

pr_wainwright

ASKER

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.

BrandonGalderisi

You're going to give a little more than "an error".

pr_wainwright

ASKER

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.

pr_wainwright

ASKER

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.

pr_wainwright

ASKER

CONVERT(datetime, @datetime,103) did the trick.

BrandonGalderisi

Yup. that's what I said to do! :)