tailieunhanh - Joe Celko s SQL for Smarties - Advanced SQL Programming P70

Joe Celko s SQL for Smarties - Advanced SQL Programming P70. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended. | 662 CHAPTER 29 TEMPORAL QUERIES There is a technical difference between a Julian date and a Julianized date. A Julian date is an astronomer s term that counts the number of days since January 1 4713 . This count is now well over 2 billion only astronomers use it. However computer companies have corrupted the term to mean a count from some point in time from which they can build a date or time. The fixed point is usually the year 1 or 1900 or the start of the Gregorian calendar. A Julianized or ordinal date is the position of the date within its year so it falls between 1 and 365 or 366. You will see this number printed on the bottom edges of desk calendar pages. The usual way to find the Julianized day within the current year is to use a simple program that stores the number of days in each month as an array and sums them with the day of the month for the date in question. The only difficult part is remembering to add one if the year is a leap year and the month is after February. Here is a very fast and compact algorithm that computes the Julian date from a Gregorian date and vice versa. These algorithms appeared as Algorithm 199 ACM 1980 and were first written in ALGOL by Robert Tantzen. Here are SQL translations of the code CREATE FUNCTION Julianizel greg_day INTEGER greg_month INTEGER greg_year INTEGER RETURNS INTEGER LANGUAGE SQL BEGIN DECLARE century INTEGER DECLARE yearincentury INTEGER IF greg_month 2 THEN SET greg_month greg_month - 3 ELSE SET greg_month greg_month 9 SET greg_year greg_year - l END IF SET century greg_year 100 SET yearincentury greg_year - 100 century RETURN 146097 century 4 1461 yearincentury 4 153 greg_month 2 5 greg_day 1721119 END Remember that the division will be integer division because the variables involved are all integers. Here is a Pascal procedure taken from Julian Dates 663 Numerical Recipes in Pascal Press et al. 1990 for converting a Georgian date to a Julian date. First you need to know the difference between .