Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

 
 

SQL Server

SQL Server Tips by Gama and Naughter Consulting

 


Implementation

As we have done so many times before, we use the XP++ class framework to implement the basic framework code for the XP DLL.

XP_ASTRO exposes 11 XP’s using the following XP++ code:

IMPLEMENT_XP(XP_ASTRO_DATE2JULIAN, CDate2JulianExtendedStoredProcedure)
IMPLEMENT_XP(XP_ASTRO_JULIAN2DATE, CJulian2DateExtendedStoredProcedure)
IMPLEMENT_XP(XP_ASTRO_DELTAT, CDeltaTExtendedStoredProcedure)
IMPLEMENT_XP(XP_ASTRO_SPRING_EQUINOX, CSpringEquinoxExtendedStoredProcedure)
IMPLEMENT_XP(XP_ASTRO_SUMMER_SOLSTICE, CSummerSolsticeExtendedStoredProcedure)
IMPLEMENT_XP(XP_ASTRO_AUTUMN_EQUINOX, CAutumnEquinoxExtendedStoredProcedure)
IMPLEMENT_XP(XP_ASTRO_WINTER_SOLSTICE, CWinterSolsticeExtendedStoredProcedure)
IMPLEMENT_XP(XP_ASTRO_MOONPHASEK, CMoonPhaseKExtendedStoredProcedure)
IMPLEMENT_XP(XP_ASTRO_MOONPHASE, CMoonPhaseExtendedStoredProcedure)

Before we dive into the details of the first 2 XP’s, we first need to describe SQL Server’s support for dates and times. SQL provides two intrinsic data types to handle this. They are “datetime” and “smalldatetime” respectively.

“datetime” stores dates between the range January 1 1753 and December 31 9999. The count of days is stored in a 4 byte integer with the value zero meaning January 1 1900. The reason for the seemingly arbitrary initial cut off date is because Great Britain and all its colonies converted from the Julian to Gregorian calendar in September 1752. In the process 12 days in the calendar were skipped to bring the calendar dates back into sync with the seasons. By limiting dates to 1753 or greater, the issue of handling this discontinuity is neatly side stepped. The time of day part is stored in a second 4-byte integer. The actual units used for the time of day part are one three hundreds of a second. The complete “datetime” data type is presented to an XP as a “DBDATETIME” structure, which is defined in the standard XP header file “srv.h” as follows:

typedef struct srv_datetime
{ // Format for SRVDATETIME
long dtdays; // number of days since 1/1/1900
unsigned long dttime; // number of 1 / 300 seconds since midnight
} DBDATETIME;

The “smalldatetime” data type uses only 2 bytes for the day and time of day parts respectively. The day part, due to its smaller size can only represent a date from January 1 1900 to June 6 2079. The final cut-off date corresponds to the maximum count of dates from January 1 1900, which can be represented in a 2 byte unsigned integer. The time of day part is also just 2 bytes in size and the unit used is minutes since midnight. A “smalldatetime” is presented to an XP using the following structure:

typedef struct dbdatetime4
{ // Format for SRVDATETIM4
unsigned short numdays; // number of days since 1/1/1900
unsigned short nummins; // number of minutes since midnight
} DBDATETIM4;
 


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

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm

 


 

 

 

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