• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

Julian Format ASAP!

I need ASAP help!
i get a date that it was totally strange for me it looks something like this 2005136 then after i did a bit of research i found that is today date, but it's expressed on Julians. Nowi need to convert this to the regular format mm/dd/yyyy in other to populate my table.

I have an access table and through a code i update those values there is any code that will do this for me automatically?

Thanks

JSCTECHY
0
jsctechy
Asked:
jsctechy
  • 10
  • 7
  • 5
  • +1
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's an MSDN article that contains a function JulianToSerial that does this for you, all you should have to do is get rid of the 1900 part to allow for yyyyddd instead of yyddd.
http://support.microsoft.com/default.aspx?scid=kb;en-us;116281

Hope this helps.
-Jim
0
 
jrb1Commented:
What DB?  If Oracle:

select to_date('01-jan-2005') + 136 - 1

For your 2005136 example.  Just convert the year to Jan 1 and add the number of days minus 1.
0
 
gabit25Commented:
You will have to create a macro with this code:

' *********************************************************************
' 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
            


http://support.microsoft.com/default.aspx?scid=KB;EN-US;209922

Bye
Gabriel
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
jsctechyAuthor Commented:
do i have to declare any string before use this values or what do i have to do here just cut a paste an a module and cal the function when i want to use it?
keep in mind that i have the day in this format 2005136 :-)
thanks
0
 
gabit25Commented:
in  the immediate window you have to type:

for 2005136:


?CJulian2Date(2005136 Mod 1000, 2005136 \ 100000)


bye
0
 
jsctechyAuthor Commented:
if i want to add this function to this current module that i'm using where should i include it thought!
take a look on the line below that is storing the CLOSE_DATE that is the variable that holds the Julian date where should i include this new function call CJulian2Date????/


Function Persh_Update_Custnew()
    Persh_Update_Custnew = 0
    On Error Resume Next
   
    Dim StrPath, StrFileName, strLine, strType, strDate, strRealDate, RealDate, InDate As String
    Dim RealDOB, InDOB As String
    Dim RealClose, InClose As String
    Dim DB As Database
    Dim rs As Recordset
    Dim P As Recordset          'NCHG
    Dim C As Recordset          'CUSTNEW
    Dim Z As Recordset
    Dim nLockCount As Integer
    Dim nRecCount As String
   
    Set DB = CurrentDb
    Set rs = DB.OpenRecordset("select strdirpath from tblDirectory;")
   
    If rs.EOF Then
        MsgBox "Cannot find path to ACCOUNT file", vbExclamation, "Path not found"
        rs.Close
        DB.Close
        Set rs = Nothing
        Set DB = Nothing
    End If
   
    If Right(Trim(rs!strDirPath), 1) = "\" Then
        StrPath = rs!strDirPath & "NCHG.asc"
    Else
        StrPath = rs!strDirPath & "\" & "NCHG.asc"
    End If
   
    rs.Close
    DB.Close
   
    StrFileName = Dir(StrPath)
   
    If StrFileName = "" Then
        MsgBox "NCHG.asc was not found", vbExclamation, "Missing"
        'GoTo Err_Persh_Update_Custnew
    End If
   
    Dim NaddVal As Recordset
   
    If ClearFiles("NCHG", "CUST_TYPE") = 1 Then        'Clear NCHG
        'GoTo Err_Persh_Update_Custnew
    End If
   
    DoCmd.SetWarnings False
   
    Set DB = CurrentDb
    Set NaddVal = DB.OpenRecordset("SELECT * from NCHG")
   
    Open StrPath For Input As #1
        Do While Not EOF(1)
            Line Input #1, strLine
            strType = Mid(strLine, 13, 1)
            If strType = "A" Then
                NaddVal.AddNew
                NaddVal![ACCT_P] = Mid(strLine, 1, 9)
                NaddVal![TIN] = Mid(strLine, 19, 9)
                NaddVal![STATE] = Mid(strLine, 35, 3)
                NaddVal![CUST_TYPE] = Mid(strLine, 40, 1)
                NaddVal![CUST_NAME] = Mid(strLine, 41, 10)
                NaddVal![REP_P] = Mid(strLine, 53, 3)
                NaddVal![PURGE_IND] = Mid(strLine, 62, 1)
                NaddVal![CSI] = Mid(strLine, 66, 1)
                NaddVal![INST_CODE] = Mid(strLine, 119, 1)
                NaddVal.Update
            ElseIf strType = "B" Then
                NaddVal.MoveLast
                NaddVal.Edit
                InDate = Mid(strLine, 14, 8)
                RealDate = Persh_Convert_Positive(InDate, 8)
                NaddVal![START_DATE] = Mid(RealDate, 5, 2) & "/" & Mid(RealDate, 7, 2) & "/" & Mid(RealDate, 1, 4)
                NaddVal![JOINT] = Mid(strLine, 46, 1)
                NaddVal![TRADAUTH] = Mid(strLine, 49, 1)
                NaddVal![CORP] = Mid(strLine, 50, 1)
                NaddVal![Partner] = Mid(strLine, 52, 1)
                NaddVal![MARGIN] = Mid(strLine, 53, 1)
                NaddVal![Option] = Mid(strLine, 55, 1)
                NaddVal![TRUST] = Mid(strLine, 57, 1)
                NaddVal![NEWACCT] = Mid(strLine, 59, 1)
                NaddVal.Update
            ElseIf strType = "C" Then
                NaddVal.MoveLast
                NaddVal.Edit
                InDOB = Mid(strLine, 14, 8)
                RealDOB = Persh_Convert_Positive(InDOB, 8)
                NaddVal![DOB] = Mid(RealDate, 5, 2) & "/" & Mid(RealDOB, 7, 2) & "/" & Mid(RealDOB, 1, 4)
                NaddVal![EMPLOY] = Mid(strLine, 76, 1)
                NaddVal![EMPLOYREL] = Mid(strLine, 77, 1)
                NaddVal![AFFIL] = Mid(strLine, 78, 1)
                NaddVal![PHONE] = Mid(strLine, 114, 10)
                NaddVal![ACCT_CATE] = Mid(strLine, 124, 4)
                InClose = Mid(strLine, 99, 7)
                RealClose = Persh_Convert_Positive(InClose, 7)
                NaddVal![CLOSE_DATE] = Mid(RealClose, 1, 8)
                NaddVal.Update
            ElseIf strType = "D" Then
                NaddVal.MoveLast
                NaddVal.Edit
                NaddVal![ADD] = Mid(strLine, 14, 32)
                NaddVal![ADD2] = Mid(strLine, 46, 32)
                NaddVal![ADD3] = Mid(strLine, 78, 32)
                NaddVal.Update
            ElseIf strType = "E" Then
                NaddVal.MoveLast
                NaddVal.Edit
                NaddVal![ADD4] = Mid(strLine, 14, 32)
                NaddVal![ADD5] = Mid(strLine, 46, 32)
                NaddVal![ADD6] = Mid(strLine, 78, 32)
                NaddVal.Update
            Else
                'Nothing
            End If
        Loop
    Close #1
   
    NaddVal.Close
    Set NaddVal = Nothing
   
    DoCmd.OpenQuery "qryUpdateCustnew1"
    DoCmd.OpenQuery "qryUpdateCustnew2"
   
    DB.Close
    Set DB = Nothing
   
    Persh_Update_Custnew = 0
   
Exit_Persh_Update_Custnew:
    varResponse = SysCmd(acSysCmdClearStatus)
    Exit Function
Err_Persh_Update_Custnew:
    If ErrorSys(Err.NUMBER, "Persh_Update_Custnew", nLockCount + 1) Then
        Resume
    End If
    Err.Clear
    On Error Resume Next
   
    Persh_Update_Custnew = 1
    Resume Exit_Persh_Update_Custnew
                   
End Function

Thanks

JSCTECHY
0
 
gabit25Commented:
You should integrate only the code in your function:

Take three variables such close_date_day and close_date_year, julday (where will put your final result)
That's the code you have to add:

close_date_day=close_date Mod 1000
close_date_year=close_date \100000
Julday = Format(DateSerial(close_date_year, 1, close_date_day), "m/d/yyyy")
0
 
jsctechyAuthor Commented:
what i did so far is:

stored the function somewhere in the main module, then
change this line to looks like this:

InClose = Mid(strLine, 99, 7)
RealClose = Persh_Convert_Positive(InClose, 7)
NaddVal![CLOSE_DATE] = Mid(RealClose, 1, 8)
CL_DATE = CJulian2DATE(InClose, 1, 8)

but i got an error saying type mistmach i this is because i get a string and i'm trying to convert to an integer as the CJulian2DATE function is saying, how can i avoid this?
oh i almost forgot the persh_convert_positive function what it does it change the last value form the file to a number, because i get from a file the day in this format 200513E so i have to change that first in order to get the regular Julian date. after the conversion i will get 2005136 where 6 is the value of E.
hence, NaddVal![CLOSE_DATE] = Mid(RealClose, 1, 8) is the value for the change Julian date but still a string what can i do???
???????????????

0
 
jsctechyAuthor Commented:
on my case what would be close_date_day? it will be NaddVal![CLOSE_DATE] = Mid(RealClose, 1, 8)
0
 
jsctechyAuthor Commented:
anything on this yet? i really need this ASAP
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>i'm trying to convert to an integer
Use CInt( your expression goes here) to convert anything to an integer.
0
 
gabit25Commented:
Sorry I was not on line for a while :)

So if tha CL_Date have to be the final result and InClose is the Julian format date in number format, I think you should do in this way:

InClose = Mid(strLine, 99, 7)
RealClose = Persh_Convert_Positive(InClose, 7)
NaddVal![CLOSE_DATE] = Mid(RealClose, 1, 8)
CL_DATE = CJulian2DATE(InClose Mod 1000, inClose /100000)


Please not that I'm not an expert in writing this kind of macros so you may have to correct the synthax error. Maybe you have to use variables for making the transformations like Mod 1000 and dividing to 100000.

Bye

0
 
jrb1Commented:
I'd missed that it was access.  I have a string field...contains 2005167:

Just do:

select CDate('01/01/' + mid(juliandate,1,4)) + mid(juliandate,5,3) - 1 from table;

Result of this is 6/16/2005, which is correct.
0
 
jsctechyAuthor Commented:
here when i'm runing the code i get only 0 everywhere!!!!!!
now i was steping the code and i see that its taking the value for instance if i have 2005133 it takes that value on the beginnnng of the function, to be more specific where it says julDay, but when it gets to the Optional YYYY i get 20.05133 and kicks out off the function any reason why??????????????
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
0
 
jsctechyAuthor Commented:
jrb1:

from what table are you saying i can take what you are loosing me here
select CDate('01/01/' + mid(juliandate,1,4)) + mid(juliandate,5,3) - 1 from table;
what is this a query a module?
0
 
jrb1Commented:
Just a query.  If you have a string that contains a julian date on your table, you can use a query to pull the value as a normal date field.  The formula:

CDate('01/01/' + mid(juliandate,1,4)) + mid(juliandate,5,3) - 1

Will convert JULIANDATE to a real date field.

To go back the other way, you'll need to reverse the process.  The formula to go back is:

CStr(Year(Date)) + CStr(Date - CDate("01/01/" + CStr(Year(Date)))+1)

0
 
gabit25Commented:
You get this result because you have to use this line instead of the old one:

CL_DATE = CJulian2DATE(InClose Mod 1000, inClose /1000)

Now you will have the good result.
0
 
jsctechyAuthor Commented:
i'm not getting this, what am i doing wrong?
I include this line: CL_DATE = CJulian2DATE(InClose Mod 1000, inClose /1000)
and i'm not getting a result.

PLEASE HELP ME, DON'T GIVE UP ON ME :-)
0
 
gabit25Commented:
try stepping the code and see where it's going out and try to tell me the error.
:)
0
 
jsctechyAuthor Commented:
gabit25
 here is my report:
this function in order to work you have to have this format: ?CJulian2Date(32, 1999) on the immediate window to get 2/1/1999

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

and my format is not that one my is 200532 for this example
so i don't think this is the formula i need!
JSCtechy
0
 
jrb1Commented:
Here's mine in your function:

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
       CJulian2Date = CDate("01/01/" & CStr(YYYY)) + JulDay - 1
End Function
0
 
jsctechyAuthor Commented:
jrb1:
what do i have to include in this part in order to work:
InClose = Mid(strLine, 99, 7)
RealClose = Persh_Convert_Positive(InClose, 7) ' here willbe 200513B which returns 2005132
NaddVal![CLOSE_DATE] = Mid(RealClose, 1, 8) ' here will be 2005132
CL_DATE = CJulian2DATE(InClose Mod 1000, inClose /100000) ' here i will get 2005.132
NaddVal![CL_DATE] = Mid(CL_DATE, 1, 8) ' to write the result on the table

what am i missing?
what is the type of CL_DATE integer or string?

Thanks
0
 
jrb1Commented:
It's a date.  Really stored as a serial number, but actual data type is date.  What format do you want the date to be in:

String as mm/dd/yyyy

NaddVal![CL_DATE] = CStr(CL_DATE)

Or you want it as a number:

yyyymmdd

NaddVal![CL_DATE] = Year(CL_DATE) * 10000 + Month(CL_DATE) * 100 + Day(CL_DATE)

Or some other format?
0
 
gabit25Commented:
You said that:

CL_DATE = CJulian2DATE(InClose Mod 1000, inClose /100000) ' here i will get 2005.132 so.... it's correct.
The type is integer so you will have to convert it.
Bye
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 10
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now