If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Converting 10 digit date from MySQL
1140373503 is an example of the date field I Imported from a MySQL database
into an Access 2003 table. How can I update this to dd-mmm-yy medium date? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200602/1 |
#2
|
|||
|
|||
Converting 10 digit date from MySQL
Hmmmm ...
This appears to be a Unix (Epoch) time value ... The conversion of .. 1140373503 to a date/time value woould be ... 2/19/2006 12:25:03 PM (central time US). Here is a user defined function to convert the value: ' ************************ Start Code ************************ ' Convert Unix Epoch time (Time in seconds since Jan 1, 1970) ' ' UTS_Offset is the hours offset from GMT where you are locate ' Eastern Time (US) = -5 ' Central Time (US) = -6 ' Mountain Time (US) = -7 ' Pacific Time (US) = -8 ' Public Function fConvertEpoch(varEpochVal As Variant, UTC_OffSet As Integer) As Variant Dim tmpDate As Date Dim StartDaylight As Date Dim EndDaylight As Date If IsNull(varEpochVal) Then Exit Function tmpDate = DateAdd("s", varEpochVal, #1/1/1970#) tmpDate = DateAdd("h", UTC_OffSet, tmpDate) ' Get the last day of March by subtracting one day from 4/1 StartDaylight = DateAdd("d", -1, DateSerial(Year(tmpDate), 4, 1)) ' Now skip to the next Sunday StartDaylight = DateAdd("d", 5 - WeekDay(StartDaylight), StartDaylight) StartDaylight = DateAdd("h", 2, StartDaylight) EndDaylight = DateSerial(Year(tmpDate), 11, 1) ' Back up to the previous Sunday EndDaylight = DateAdd("d", -5 + WeekDay(EndDaylight), EndDaylight) EndDaylight = DateAdd("h", 1, EndDaylight) If (tmpDate = StartDaylight And tmpDate EndDaylight) Then tmpDate = DateAdd("h", 1, tmpDate) End If fConvertEpoch = tmpDate End Function ' ************************ End Code ************************ Here is an example of the usage: =fConvertEpoch(1140373503,-6) The above is using Central Time (US) ... R. Hicks -- Ricky Hicks - Access MVP "Scott Miller via AccessMonster.com" u17998@uwe wrote in message news:5c220a19beca0@uwe... 1140373503 is an example of the date field I Imported from a MySQL database into an Access 2003 table. How can I update this to dd-mmm-yy medium date? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200602/1 |
#3
|
|||
|
|||
Converting 10 digit date from MySQL
Neat function, Ricky! It would be even better if it got the daylight
saving information from the workstation instead of hard-coding it. That way it would work world-wide, not just in most of North America (see e.g. http://webexhibits.org/daylightsaving/g.html). On Sun, 19 Feb 2006 17:51:44 -0600, "Ricky Hicks [MVP]" wrote: Hmmmm ... This appears to be a Unix (Epoch) time value ... The conversion of .. 1140373503 to a date/time value woould be ... 2/19/2006 12:25:03 PM (central time US). Here is a user defined function to convert the value: ' ************************ Start Code ************************ ' Convert Unix Epoch time (Time in seconds since Jan 1, 1970) ' ' UTS_Offset is the hours offset from GMT where you are locate ' Eastern Time (US) = -5 ' Central Time (US) = -6 ' Mountain Time (US) = -7 ' Pacific Time (US) = -8 ' Public Function fConvertEpoch(varEpochVal As Variant, UTC_OffSet As Integer) As Variant Dim tmpDate As Date Dim StartDaylight As Date Dim EndDaylight As Date If IsNull(varEpochVal) Then Exit Function tmpDate = DateAdd("s", varEpochVal, #1/1/1970#) tmpDate = DateAdd("h", UTC_OffSet, tmpDate) ' Get the last day of March by subtracting one day from 4/1 StartDaylight = DateAdd("d", -1, DateSerial(Year(tmpDate), 4, 1)) ' Now skip to the next Sunday StartDaylight = DateAdd("d", 5 - WeekDay(StartDaylight), StartDaylight) StartDaylight = DateAdd("h", 2, StartDaylight) EndDaylight = DateSerial(Year(tmpDate), 11, 1) ' Back up to the previous Sunday EndDaylight = DateAdd("d", -5 + WeekDay(EndDaylight), EndDaylight) EndDaylight = DateAdd("h", 1, EndDaylight) If (tmpDate = StartDaylight And tmpDate EndDaylight) Then tmpDate = DateAdd("h", 1, tmpDate) End If fConvertEpoch = tmpDate End Function ' ************************ End Code ************************ Here is an example of the usage: =fConvertEpoch(1140373503,-6) The above is using Central Time (US) ... R. Hicks -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Outlook today should show tasks based on Start Date, not due date | Rose | General Discussion | 10 | October 9th, 2005 03:14 AM |
NETWORKDAYS - Multiple Date Selection | Annabelle | General Discussion | 3 | October 4th, 2005 07:04 PM |
Converting Text Date to true Date format | Anthony C | Running & Setting Up Queries | 1 | November 24th, 2004 03:39 PM |
QDE (Quick Date Entry) | Norman Harker | General Discussion | 3 | September 3rd, 2004 08:00 AM |
Aggregating Date Data into Weeks and Quarters | Roger | Running & Setting Up Queries | 3 | July 11th, 2004 05:56 PM |