Converting Epoch Time to Readable Date

maigoro · April 9th, 2008, 08:12 PM

I have a column that stores the number of seconds the number of seconds since Jan 1, 1970

My Grid looks like this in my code -

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"

CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="Black"

GridLines="Vertical" AllowPaging="True" AllowSorting="True">

<FooterStyle BackColor="#CCCC99" />

<RowStyle BackColor="#F7F7DE" />

<Columns>

<asp:BoundField DataField="Last_Modified_Date" HeaderText=Last Modified Date"

SortExpression="Last_Modified_Date" />

</Columns>

<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />

<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />

<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />

<AlternatingRowStyle BackColor="White" />

</asp:GridView>

It works fine but I would like is to convert the seconds that I get back into readable time..

So I thought of DateAdd(s,Last_Modified_date, January 1 1970)

Is that possible? Is there another way. Thanks

maigoro · April 10th, 2008, 12:14 PM

I have fixed this issue by using a DateAdd function in the query.

Select DateAdd(ss,Last_Modified_Date,01-01-1970)AS My_Modified_Date from .....

and my Gridview column becomes

<asp:BoundField DataField="My_Modified_Date" HeaderText="Modified Date"
SortExpression="My_Modified_Date" />

Cheerio.

-Ina Gaida Binta Single Baby

maigoro · April 12th, 2008, 11:42 AM

I found a huge issue - My dates were off by 70 years (depending on time zone plus or minus a few hours).

So I had to do this..

SelectCommand="
            DECLARE @MyEpochDate DATETIME
            SET @MyEpochDate='1969-12-31 7:00 PM'
            SELECT Q.StatusLookup, H.Total_Admin_Effort_in_Minutes, H.Total_Labor_Effort_in_Minutes, H.Company, H.Last_Name, H.First_Name, H.Person_ID, H.Detailed_Decription, H.Resolution, DateAdd(s,H.Submit_Date,@MyEpochDate) AS My_Submit_Date, H.Incident_Number FROM HPD_Help_Desk H, QTSLookupStatus Q WHERE H.Status = Q.Status AND (H.Last_Name = @Last_Name) AND (H.Person_ID = @Person_ID) ORDER BY Incident_Number"

-Ina Gaida Binta Single Baby