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



Because astronomical calculations can be done for dates outside of the date ranges of both “datetime” and “smalldatetime”, most of the XP_ASTRO functions use a different type of date time representation to provide for added flexibility. This in itself is an arbitrary design decision for this DLL. You might want to provide direct support for SQL date time data types in your XP’s, which manipulate date time data types as opposed to the approach used in XP_ASTRO, which is to first convert to an intermediate date representation. In normal astronomical calculations, a so-called “Julian Day” is used. This is the number of days (possibly including fractional parts of a day) since a certain point in time. This point in time was chosen to be 1 January -4712 (or 4713 BC / BCE). This arbitrary date was picked to ensure Julian Days for most events of historical or astronomical significance would always be positive.

XP_ASTRO_DATE2JULIAN takes one input parameter, which can be a “datetime” or “smalldatetime” and converts it to the Julian Day. As already discussed since both SQL date data types already are principally a count of days, it is relatively easy to convert to a Julian Day. To convert a “datetime” to a Julian day, the following code is used:

double dblJulian = JULIAN_1900 + pDT->dtdays + (pDT->dttime / ONE3HUNDREDSECONDS_IN_A_DAY);

The constant “JULIAN_1900” corresponds to the Julian Day for 1 January 1900 (2415020.5). The pointer variable pDT is the incoming parameter of type “DBDATETIME”, and the constant “ONE3HUNDREDSECONDS_IN_A_DAY” is the number of three hundreds of a second in one day (25,920,000.0 = 24 * 60 * 60 * 300). One quirk with the Julian Day is that by convention the Julian Day begins at midday, hence the reason for the “.5” in the “JULIAN_1900” constant.

To convert from a “smalldatetime” to a Julian Day, the following code is used:

double dblJulian = JULIAN_1900 + pDT->numdays + (pDT->nummins / MINUTES_IN_A_DAY);

The main difference between this code and the “datetime” code is that the time of day part is divided by 1440 (24 * 60).

The corollary XP to XP_ASTRO_DATE2JULIAN is XP_ASTRO_JULIAN2DATE and this performs the reverse operation i.e. convert from the Julian Day back to a “datetime” or “smalldatetime”. The code to handle the first conversion is:

DT.dtdays = (DBINT) (*pdblJulian - JULIAN_1900);
double intDay;
double fractionalDay = modf(*pdblJulian + 0.5, &intDay);
DT.dttime = (ULONG) (fractionalDay * ONE3HUNDREDSECONDS_IN_A_DAY);

The “DT” variable will be returned to SQL Server following this code snippet as the output parameter using “CExtendedStoredProcedure::ParamSetOutput”.

The code to handle the second conversion is:

DT.numdays = (USHORT) (*pdblJulian - JULIAN_1900);
double intDay;
double fractionalDay = modf(*pdblJulian + 0.5, &intDay);
DT.nummins = (USHORT) (fractionalDay * MINUTES_IN_A_DAY);

The XP_ASTRO_JULIAN2DATE code also demonstrates how to directly access an XP’s parameters without needing to use the parameter conversion support in the XP++ framework. In this XP, to access the input parameter, which is the Julian Day, we can simply use the following code:

double* pdblJulian = (double*) m_pParameterData[JULIAN2DATE_JULIAN].m_pData;

You can use similar code in your XP’s if you want to avoid the overhead of using the parameter conversion support in XP++. Please note that you should be careful when using this as using this code for a string input parameter would mean that the parameter would not be NULL terminated and if you were to use the standard C string runtime functions (which expect a NULL terminated string), then you would end up causing an access violation. Also directly accessing a parameter only makes sense when the parameter can only be of one possible data type.

Hopefully these 2 XP’s should give you a firm understanding on how you can manipulate date time data types in your own XP’s. If you want to go beyond the functionality demonstrated here, then you will probably want to develop some classes to encapsulate date times or uses existing ones such as those provided by MFC or third party authors. For example to obtain the current day of the month for an incoming “datetime” variable you could use the “CAADate” class provided in the AA+ framework with the following code:

CAADate date(JULIAN_1900 + pDT->dtdays + (pDT->dttime / ONE3HUNDREDSECONDS_IN_A_DAY));
long nDay = date.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