00571: How to display JULIAN dates in other formats via the ODBC Driver

Title:

How to display JULIAN dates in other formats via the ODBC Driver

Description:

Many BBx applications store date values in data files as a Julian number. A Julian date is defined as the number of days passed since January 1, 4713 B.C. (By definition, a Julian date changes at noon. PRO/5 will change at midnight.) Since the days are sequentially numbered it is a convenient way to determine the number of days between any two dates. However, because Julian dates tend to be so large (for current dates), it isn’t easy for humans to translate a Julian number into something that makes sense. Most of the time, we deal with dates in a format that specifies Days, Months and Years. When reporting on date values, it’s often desirable to translate the Julian date into a format a format that uses Days, Months and Years instead. 

This can be done via two methods: 

1) The first method is to instruct the BASIS ODBC Driver to automatically convert the Julian Dates for you. This is configured in the Advanced section of the Windows ODBC Administrator configuration for the Data Source. By default, any numeric column that ends in the word ‘date’ will be treated as a Julian and converted to the ‘yyyy-mm-dd’ format. There is a field in the ODBC Admistrator called Date Column Suffix that allows you to configure which columns will be treated as SQL DATEs. Any column name that ends in the Date Column Suffix will be treated as a date. For example, if the Date Column Suffix is DATUM and you have a field named MY_DATUM, this field will be treated as an SQL_DATE since it ends in the Date Column Suffix. When the ODBC Driver is configured to translate dates like this, a Julian date of 2450814 will be printed as 1997-12-31. 

2) The second method is to use some of the ODBC Driver’s built-in functions to manipulate the dates. One way of doing this is via the DATE() function. The ODBC Driver’s DATE() function is similar to PRO/5’s and allows for many formatting options. As an example, to convert Julian dates such as 2450814 to a format like 12/31/1997, you could perform the following query: 

SELECT date(sale_date,’%Mz/%Dz/%Yz’) as sale_date FROM sales_table 

The ODBC Driver’s on-line manual gives more examples and covers the options available to the DATE() function.

Last Modified: 02/13/1998 Product: BASIS ODBC Driver Operating System: Windows

BASIS structures five components of their technology into the BBx Generations.