Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu





SQL Server

SQL Server Tips by Gama and Naughter Consulting



The final two XP’s we provide in the XP_ASTRO DLL concern the calculation of the phases of the Moon. The 4 phases of the Moon are Full Moon, Last Quarter, New Moon and First Quarter. Full Moon occurs when the Moon is in the same direction of the Sun as measured in the plane of the Earth’s orbit, with the Earth being the middle object of the trio. If the 3 objects are exactly in line, then a Lunar Eclipse is produced. Last Quarter occurs after a Full Moon and is when the terminator (which is the edge of the Suns shadow on the Moon) is a straight line and divides the lit and unlit sides of the Moon into two semi circles. In geometric terms, Last Quarter occurs when the Moon, Earth & Sun form a right-angled triangle. New Moon is the corollary to a Full Moon and occurs when the three objects are in a line, but this time, the Moon is the middle object. If the three objects form an exact line, then a Solar Eclipse takes place. First Quarter occurs following New Moon and is similar to Last Quarter, except this time the lit and unlit hemispheres are swapped.

The algorithms which these two XP’s use are based on formulae where each set of moon phases can be calculated one at a time, using a polynomial series where each subsequent phase can be determined by incrementing a simple numerical constant “k”. If we take a specific integer value of “k”, then we obtain a New Moon, while incrementing by 0.25 gives a First Quarter, 0.5 gives a Full Moon and 0.75 gives a Last Quarter. This formula is implemented in the AA+ framework function “CAAMoonPhases::MeanPhase”. To this instant a number of corrections are applied to arrive at the true time of the Moon phase. This functionality is provided by “CAAMoonPhases::TruePhase“.

The first XP namely XP_ASTRO_MOONPHASEK allows you to obtain an approximate value for “k” given a year number with fractions. Internally this XP uses the AA+ function “CAAMoonPhases::K” for this calculation. For example to find the value of “k” to use for the New Moon, which took place in February 1977, we would use:

DECLARE @MoonPhaseDate float
SELECT @MoonPhaseDate = 1977.13
DECLARE @MoonPhaseK float
EXEC master..XP_ASTRO_MOONPHASEK @MoonPhaseDate, @MoonPhaseK OUTPUT

This will print the value -282.87, so to obtain the New Moon we should use the value -283 (which is the closest integer value to -282.87). This value is passed to the second XP namely XP_ASTRO_MOONPHASE to obtain the date of the required Moon phase. As with the other XP’s we have presented, the returned value from the second XP is a Julian Ephemeris Day.

The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter





Burleson Consulting Remote DB Administration







Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter