Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert Julian date to 12 month calendar date

Posted on 2014-09-15
10
Medium Priority
?
654 Views
Last Modified: 2014-09-16
I'm using Access to connect to the Pervasive database using ODBC. I have some tables that contain a Julian Calendar date which I need to convert to a regular 12 month calendar date

PERIOD_01 = 2012213 which is August the 1st 2013 and I want in date format.

I'm using this Julian calendar here for reference. http://www.fs.fed.us/fire/partners/fepp/julian-calendar.pdf
0
Comment
Question by:Gerhardpet
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40323972
see the codes from this page from M$
How to Convert Julian Days to Dates
0
 
LVL 29

Expert Comment

by:Bill Bach
ID: 40323981
Use the PSQL function DateAdd() to subtract the known value (supply the value as a nagative) from the known date. This will give you the starting date for the calendar. From there, use the same function, but supply the starting date and use positive values, and it should calculate it for you. If you are not sure how to use the DateAdd function, start the Pervasive Control Center, then help/docs, then search for DateAdd.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40324035
Here is a one-liner:

j = 2013213
datDate = DateSerial(j \ 1000, 1, j Mod 1000)

=> 2013-08-01

/gustav
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:Gerhardpet
ID: 40324128
I would rather do this in Access since I'm able to do much with my queries that I can when running them in PCC

@Gustav Brock: Can you give me a bit more details? Not sure what to do with what you have posted.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40324138
Don't know what you wish to do, but for example a Query:

Select *, DateSerial([YourDateFieldName] \ 1000, 1, [YourDateFieldName] Mod 1000) As TrueDate
From tblYourTable

/gustav
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40324241
@Gerhardpet

did you look at the codes from the link I posted?



.
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40324450
@gustav.

That worked

How would I add + 1 now? I want the date +1 day?

@ Rey - Yes I did but I mostly rely on Access to do the queries for me so I could not make much out of the link.
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40324842
That's easy. Modify the day parameter of DateSerial:

Select *, DateSerial([YourDateFieldName] \ 1000, 1, [YourDateFieldName] Mod 1000 + 1) As TrueDate
From tblYourTable

/gustav
0
 
LVL 1

Author Closing Comment

by:Gerhardpet
ID: 40326729
@gustav

That worked great! Thanks for your help!
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40327327
You are welcome!

/gustav
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question