[RESOLVED] saving date and time in DB

Hi,

I'm having an issue. By using the following code, i save the dateandtime to my MSSQL DB .

Code:

---

Dim dateTimeToConvert As New DateTime(Now.Year, Now.Month, Now.Day, Now.Hour, Now.Minute, Now.Second, Now.Minute, DateTimeKind.Utc)
        Dim easternTime As DateTime = TimeZoneInfo.ConvertTime(dateTimeToConvert, easternZone)
        Dim nowtime As DateTime = easternTime.ToUniversalTime

 TIMESTAMPSqlDataSource1.InsertParameters("datee").DefaultValue = nowtime

TIMESTAMPSqlDataSource1.insert()

---

In the DB it saves the time as 21:.... but when I view the time using a gridview it shows 1:... (the correct time I want to show is the 1:... )

HTML Code:

---

<form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" />
                <asp:BoundField DataField="datee" HeaderText="datee" SortExpression="datee" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnString %>" SelectCommand="SELECT [ID], [datee] FROM [timestamp] ORDER BY [ID] DESC"></asp:SqlDataSource>
    </form>

---

I'm not sure why i'm getting two different dates , any idea?

thanks

You are converting the value to UTC before you store it... so is the time of your database server 4 hours (or 19) away from UTC?

Hi,

BTW the time in the DB is in 24hr format, ,and the time in gridview is in 12hr format

I just executed the following command on my DB and the result is as follow :

Code:

---

SELECT CURRENT_TIMESTAMP
GO
SELECT {fn NOW()}
GO
SELECT GETDATE()
GO

---

Code:

---

2013-09-03 19:46:11.333
2013-09-03 19:46:11.407
2013-09-03 19:46:11.463

---

The DB time follows Eastern Time Zone .

Just right now i saved something on db and the time is saved as 2013-09-03 15:51:20.000 and shows on the grid view as 03-09-03 2013 7:51:20 PM

And as I mentioned earlier the DB uses EST which is the time zone i want, so i made the default value of the datee field the current date and this is done auto by SQL, VB will submit no value to the DB.
Result, it saved in EST time in the DB but when I check it on gridview again i get a different value.

I found the problem. My bad i was using the following formatter somewhere in my page, as soon as I removed it started workign fine {0:U}

Thank You for ur time