MS Access: convert seperate year, day_count and time fields to a date/time

2
votes

I have a text file created by another program that I have imported into MS Access 2003. The date & time fields are split into 3 seperate fields and I would like to create 1 date/time field. Here is an example of the data:

YEAR,DAY_NUMBER,TIME
2002,231,223405
2004,117,000000

YEAR: The year in YYYY format

DAY: The day of the year. e.g. 001 would represent 1st of January; 034 would be the 3rd of February

TIME: The time in HHMMSS

How do I create an additional field with the corresponding date/time value? Thanks in advance.

3
ms-accessdatetime

3 Answers

2
votes

This example assumes your table is named MyTable, its YEAR and DAY_NUMBER fields are both numeric data types, the TIME field is text, and you added a Date/Time field called date_time.

Try an update query with the DateSerial() and TimeSerial() functions.

UPDATE MyTable
SET date_time = DateSerial([YEAR],1,DAY_NUMBER)
    + TimeSerial(Left([TIME],2), Mid([TIME],3,2), Right([TIME],2));

Edit: If I misunderstood your intention, and you don't actually need or want to store the Date/Time value, you can just derive it when needed with a SELECT query.

SELECT
    DateSerial([YEAR],1,DAY_NUMBER)
        + TimeSerial(Left([TIME],2), Mid([TIME],3,2), Right([TIME],2))
        AS date_time
FROM MyTable;
1
votes
' *********************************************************************
' FUNCTION: CJulian2Date()
'
' PURPOSE: Convert a Julian day to a date. The function works with
'          dates based on the Gregorian (modern) calendar.
'
' ARGUMENTS:
'    JulDay: The ordinal day of a year. Between 1 and 365 for all
'            years, or between 1 and 366 for leap years.
'
'    YYYY: A three or four digit integer for a year that is within the
'          range of valid Microsoft Access dates. If YYYY is omitted,
'          then YYYY is assumed to be the year of the current system
'          date.
'
' RETURNS: A date for a valid Microsoft Access year and Julian day,
'          or a Null value for an invalid Julian Day.
' *********************************************************************

Function CJulian2Date (JulDay As Integer, Optional YYYY)
    If IsMissing(YYYY) Then YYYY = Year(Date)
    If Not IsNumeric(YYYY) Or YYYY \ 1 <> YYYY Or YYYY < 100 Or YYYY _
      > 9999 Then Exit Function
    If JulDay > 0 And JulDay < 366 Or JulDay = 366 And _
      YYYY Mod 4 = 0 And YYYY Mod 100 <> 0 Or YYYY Mod 400 = 0 Then _
        CJulian2Date = Format(DateSerial(YYYY, 1, JulDay), "m/d/yyyy")
End Function

Source: http://support.microsoft.com/kb/209922

0
votes

Take the year and convert to Jan 1 of that year. Add the number of days to that and convert to a date format. You could put this in a query.

CalcDate: CDate(CLng(CDate("01/01/" & [Year]))+[day]-1)