When querying or working with data based on datetime, date, or time values Sybase has a group of Date functions available to help perform arithmetic operations.  These functions include GETDATE, MONTH, YEAR, DATEADD, and DATEDIFF.

To help illustrate some of these functions lets assume we have the following ITEM, ORDERS, and ORDERS_ITEM tables that contains the following columns and data.

How to obtain the current date and or time

The GETDATE function is used to get the current system date and time. Lets assume that we want to obtain a report of the orders that were made today, to do so we would want to compare the ORDERS.BUY_DATE column with the current date. As our table is not storing the time and only the date we would make use of the current_date function to only get the date (likewise the current_time function returns the time).

SELECT * FROM ORDERS WHERE BUY_DATE = current_date()

Assuming today’s date was February 3rd, 2009 we would have the following results:

Modifying the time using DATEADD

Now that we know how to get the current date and time in a Sybase T-SQL, lets assume we have a report that runs every day in the morning that reports the orders that were made the previous day. To do this we can modify our previous example to compare the date against the current date – 1 day. Sybase provides the DATEADD function to modify the date.

SELECT * FROM ORDERS
WHERE BUY_DATE = dateadd( dd, -1, current_date() )

The DATEADD function takes 3 parameters the part of date to be modified, value to modify by, and the date to be modified.
Assuming today’s date was February 4th, 2009 we would have the same results as the previous example

Utilizing the month and year parts of a Date

Sybase provides two convenient methods for obtaining the month or year part of a Date. These functions can be used to query data for a particular period of time.  Both the MONTH, and YEAR functions take a single parameter and that is the date for which the month or year should be returned.

For our first example lets assume we want to know which items and how many were sold for the current month. To do this we will need to join the ORDERS and ITEM tables based on the ORDERS_ITEM linking table and return the sum of the quantity that were sold for each order.

SELECT i.NAME, sum(oi.QUANTITY) as NUM_SOLD
FROM ORDERS o
  JOIN ORDER_ITEM oi ON o.ID = oi.ORDER_ID
  JOIN ITEM i ON i.ID = oi.ITEM_ID
WHERE MONTH (o.BUY_DATE) = MONTH( current_date() )
GROUP BY i.NAME

Running this query assuming that the current date is the last day of December, 2008 would return the following results.

Sybase provides additional Date functions besides those mentioned above to familiarize yourself with them on I suggest accessing their online manual which can be accessed under References.

References

• Sybase – Manual : Date functions