acessing date as dd/mm/yyyy

[kanuri1]

hi frnds,
i had gota problem,
i get the date in mm/dd/yyyy format only...........
when iam accessing date from database in dd/mm/yyyy format itg gets error..

the error is as "Arithmetic overflow error converting expression to data type datetime."

here my code is given below

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
            BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataSourceID="SqlDataSource1"
            Style="z-index: 103; left: 393px; position: absolute; top: 251px">
            <RowStyle BackColor="White" ForeColor="#330099" />
            <Columns>
                <asp:BoundField DataField="date_of_arr" HeaderText="date_of_arr" SortExpression="date_of_arr" />
                <asp:BoundField DataField="date_of_dept" HeaderText="date_of_dept" SortExpression="date_of_dept" />
                <asp:BoundField DataField="DAYS" HeaderText="DAYS" ReadOnly="True" SortExpression="DAYS" />
            </Columns>
            <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
            <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:guest1 %>"
            SelectCommand="SELECT date_of_arr, date_of_dept, DATEDIFF(dd, date_of_arr, date_of_dept) AS DAYS FROM guesthouse WHERE (date_of_arr = @date_of_arr)">
            <SelectParameters>
                <asp:ControlParameter ControlID="arrivaldate"  Name="date_of_arr" PropertyName="Text" />
            </SelectParameters>
        </asp:SqlDataSource>

here iam using asp.net with vb.net........
database is sqlserver-2005..........

[Oxiegen]

Change this

SelectCommand="SELECT date_of_arr, date_of_dept, DATEDIFF(dd, date_of_arr, date_of_dept) AS DAYS FROM guesthouse WHERE (date_of_arr = @date_of_arr)">

Into this

SelectCommand="SELECT CONVERT(DateTime,date_of_arr,103), CONVERT(DateTime,date_of_dept,103), DATEDIFF(dd, date_of_arr, date_of_dept) AS DAYS FROM guesthouse WHERE (CONVERT(DateTime,date_of_arr,103) = @date_of_arr)">

Notice the CONVERT functions.
The CONVERT function takes 2-3 arguments.
1: The target datatype of the field
2: The fieldname
3: The format of the output

Here's an overview of how to format dates in SQL: http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

[reach_yousuf]

hi frnds,
i had gota problem,
i get the date in mm/dd/yyyy format only...........
when iam accessing date from database in dd/mm/yyyy format itg gets error..

the error is as "Arithmetic overflow error converting expression to data type datetime."

here my code is given below

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataSourceID="SqlDataSource1"
Style="z-index: 103; left: 393px; position: absolute; top: 251px">
<RowStyle BackColor="White" ForeColor="#330099" />
<Columns>
<asp:BoundField DataField="date_of_arr" HeaderText="date_of_arr" SortExpression="date_of_arr" />
<asp:BoundField DataField="date_of_dept" HeaderText="date_of_dept" SortExpression="date_of_dept" />
<asp:BoundField DataField="DAYS" HeaderText="DAYS" ReadOnly="True" SortExpression="DAYS" />
</Columns>
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:guest1 %>"
SelectCommand="SELECT date_of_arr, date_of_dept, DATEDIFF(dd, date_of_arr, date_of_dept) AS DAYS FROM guesthouse WHERE (date_of_arr = @date_of_arr)">
<SelectParameters>
<asp:ControlParameter ControlID="arrivaldate" Name="date_of_arr" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>

here iam using asp.net with vb.net........
database is sqlserver-2005..........

Convert your date columns(date_of_arr, date_of_dept) while select and insert in same pattern dd/mm/yyyy

SELECT convert(varchar(10), CAST( date_of_arr AS DATETIME) , 103)

Mark as solved if it helps you!!!

[mail2saion]

Yes you can use the technique to convert first date in a textual format for displaying & use built in function to get day difference. There are a lot of styles available for converting datetime to string. Please Google if needed.

[Oxiegen]

Quite right.
You can also change the format of the date in the gridview.
But if you change the format of the date at the source, you only have to do it once and makes for, in my opinion, cleaner code.