[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Using Julian Dates in MS Access ADP & SQL

In an access MDB I had a field that gave a projected End date:

ProjEndDate: Date()+[RDRByVol]+((([RDRByVol]/5)-1)*2)

From doing some research I realize now that Access handled the julian conversion of Date() above.  However, I can't seem to understand anything I'm reading about how do to this in SQL or an ADP.  I understand that you can't use a get date in a function in SQL.  So how do I get the date in Julian Date to use above?  I need as much detail as possible because I really don't understand much of what I'm reading about Julian Dates in SQL.  Thanks
0
SCS1ST
Asked:
SCS1ST
  • 6
  • 6
  • 2
2 Solutions
 
flavoCommented:
Just pass GETDATE() into the function too:

CREATE FUNCTION myFunction
(
@date DATETIME,
@RDRByVol INT
)

RETURNS DATETIME
AS
BEGIN
   RETURN -- Add your formula here
END


Then call it like:

SELECT dbo.myFunction(GETDATE(), 1234)

Idea?

Dave  
0
 
nico5038Commented:
Hmm, internally Access uses a relative day number (number of days after 12/30/1899) and the above calculation will just be executed by simple adding the RDRByVol and the result of ((RDRByVol/5)-1)*2
The RDRByVol value is however "unknown" for me and I would also like to know what you see as Julian date.
For me it's a date like 2006 - 123 being the format yyyy-ddd where ddd is the daynumber since 1th of January.

Nic;o)
0
 
flavoCommented:
Oh.. Here's an article all about it the GETDATE() / UDF mayhems
>> How do I use GETDATE() within a User-Defined Function (UDF)?:
>> http://www.aspfaq.com/show.asp?id=2439
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
SCS1STAuthor Commented:
flavo - Thank you so much for the link and your help above.  Unfortunately I wasn't able to create the function.  I got the errors:  Error#207  Invalid column name date, Invalid column name RDRByVol, Invalid column name RDRByVol.  I'm starting to understand more about julian dates and what it is and a little how it's used, but I still can't seem to get it to connect with what I need to do.    I was able to create the function dbo.addDay from the example in the link.  And when supplied with a date it adds a day.  I was originally trying to get the ProjEndDate in the query that creates this report.  Then I started thinking about making it unbound and doing it through code.  That's what I got from your suggestions above.  However, I still can't get it to work.

Nico - I wasn't exactally sure what I saw as a julian date.  I guess I thought it was a calculation of some sort for a given day?  Your example was very helpful.  Thank you.  RDR stands for RigDaysRemaining and ByVol% is show below.  An example of the value of each follows each example.  

CASE WHEN [ByVol%] > 0 THEN ((100 - [ByVol%]) * [DailyRpt]) / [ByVol%] ELSE [EstWorkDays] END  as RDRByVol     ie. 41799.2653649595

CASE WHEN masterbid.totcf > 0 THEN (sumofVolTot / masterbid.totcf) * 100 ELSE 0 END  as ByVol%   ie  0.0287003990318283

I hope this information helps you to understand more of what I need.  

0
 
nico5038Commented:
Hmm, not really, but you should be able to use in a query:

select Date()+[RDRByVol]+((([RDRByVol]/5)-1)*2) as JulDate, ..... from tblX

Just make sure that the RDRByVol field is in the tblX

Nic;o)
0
 
SCS1STAuthor Commented:
But that's just it.  RDRbyVol is a calculated field in another function.  I tried to create what you suggested in a function and it wouldn't let me.  However, I did it in a view and it starts and I can see some of the output before it gives me the following error:
Arithmetic overflow error converting expression to datatype datetime.  If I could get it past this point and get it in a view I could use it for the report.  Any ideas?
0
 
SCS1STAuthor Commented:
Oh, I forgot to mention that I had to change the expression to this as it wouldn't accept date():

{ fn NOW() } + RDRByVol + (RDRByVol / 5 - 1) * 2 AS JulDate
0
 
nico5038Commented:
What's the RDRByVol value when you get this error ?

Nic;o)
0
 
SCS1STAuthor Commented:
I can't tell exactally.  By the time it errors out it won't let me view the last record and once I click on the error all the data swithes to #Name?.  one of the last ones that I can see on the screen is 66994.124653384   Is there another way for be to be able to see it that I'm not aware of?
0
 
nico5038Commented:
Hmm, the value you give indicates #02/06/2083# and when you get an error that would imple that the result is larger as 2,147,483,647 being the max value you can store in your date field.

Can you post the funtion that's calculating RDRbyVol ?
Somewhere this is "freaking out".
When you know the fields that are used by this function we can probably look further.

Nic;o)
0
 
SCS1STAuthor Commented:
This creates the RDRByVol:  
SELECT     JobsQ_FasterB.*, CASE WHEN [ByVol%] > 0 THEN ((100 - [ByVol%]) * [DailyRpt]) / [ByVol%] ELSE [EstWorkDays] END AS RDRByVol
FROM         dbo.JobsQ_FasterB() JobsQ_FasterB

ByVol% is calculated in another function.  Here's the ByVol% calculation .  

***CASE WHEN masterbid.totcf > 0 THEN (sumofVolTot / masterbid.totcf) * 100 ELSE 0 END***

totcf is a float, 8 field;  sumofVolTot is another calculation:  SUM(VolTot) AS SumOfVolTot;  VolTot is yet another calculation from anoter function -  VolShaft + VolBell AS VolTot,;   VolShaft & VolBell are more calculations below:

volshaft   =  3.141593 * POWER(r1, 2) * DrilledDepth

volbell =  CASE WHEN BellDiam > 0 THEN [Vc] - [Vs] + [Vt] ELSE 0.1524 END

This is getting a bit convulted.  Think I'm starting to see why I'm having problems. haha To Continue...

r1 = CASE UnitSystem WHEN 0 THEN CONVERT(FLOAT, shaftdiameter / 24) ELSE CONVERT(FLOAT, shaftdiameter / 2000) END

r2 = CASE UnitSystem WHEN 0 THEN CONVERT(FLOAT, BellDiam / 2) ELSE CONVERT(FLOAT, BellDiam / 2000) END

Vc = CASE WHEN r2 > 0 THEN (1.8138 * ([R2] - [R1]) * (POWER([R1], 2)) + (POWER([R2], 2)) + ([R1] * [R2])) ELSE 0 END

Vs = CASE WHEN R2 > 0 THEN (5.4416 * ([R2] - [R1]) * (POWER([R1], 2))) ELSE 0 END

Vt = 3.141593 * d * POWER(r2, 2)

Now that I've gotten everyone totally confused....wondering if it wouldn't be easier to go back to trying to put the projenddate code on the report somewhere.


0
 
nico5038Commented:
Yep, I have a hard time understanding how the enddate has to be deducted from these calculations.
Looks to me that the drillshaftdiameter and the DrilledDepth determine the estimated days needed and I detect Pi in the calculation to get the square inches(?) :-)
The rest is confusing as I would expect to have an estimated number of days and the calculation should somehow compare the estimated days with the realized days, but that's what I'm "missing"

Nic;o)

0
 
SCS1STAuthor Commented:
For anyone looking into this question for a solution, I ended up putting the code back on the report to get the end project date.  Nico's solution I believe would have worked, other than the calculations that were creating these fields were just too complex and would therefore hang while running the report.  I'm giving the points becasue both of them gave me information that was extremely helpful in helping me to understand Julian Dates.  
0
 
nico5038Commented:
Thanks and success with your application!

Nic;o)
0

Featured Post

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.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now