Current Date To The Nearest Month example essay topic

Dates in Oracle are stored in numeric format. Each date represents the century, year, month, day, hours, minutes, and seconds. The default date format is DD-MON-YY. Using the SYSDATE function, you can display the current date and time. You can use SYSDATE as you would use any other column name.

In order to view the time you will need to use the TO CHAR function along with a format mask which includes a time format element. For example, you can display the current date by selecting SYSDATE from a table. You usually select SYSDATE from a dummy table called DUAL. All users can access the DUAL table.

It is owned by the SYS user. It consists of one column known as DUMMY, and one row with the value X. When you want to return a value - not necessarily a date - once only, the DUAL table is useful. To display the current date using the DUAL table, you use the command shown. And the output displays the current date.

Dates are stored as numbers in the database, which enables you to perform arithmetical calculations on them. You can add or subtract a number to or from a date for a resultant date value. You can subtract one date from another to find the number of days between them. You can also add hours to a date and time value.

This is performed by dividing the number of hours in the time value by 24. Let's take a look at how you can manipulate dates. Let's say that you want to add and subtract days from the current date. To add one hundred days to the current date, you type the command shown. The result is displayed in the column SYSDATE+100.

If you want to subtract 34 days from the current date, you type the command shown. The result is displayed in the column SYSDATE-34. Let's say you want to subtract the hire date from the current date - you type the command shown. The result shows the number of days between the current date and the hire date. Let's say you want to display dates in terms of weeks. To show the number of weeks each employee in department 10 has worked, you first subtract the hire date from the current date.

Then you divide the result by seven. You can use date functions in a similar way to number functions to operate on Oracle dates. The MONTHS BETWEEN function finds the number of months between two dates. The result is displayed as a numeric value. This is the only date function that does not return a date value. The syntax for MONTHS BETWEEN is shown - date 1 and date 2 are the dates that you want to find the number of months between.

The result displayed can be either positive or negative. If date 1 is later than date 2, the result is positive. And if date 1 is earlier than date 2, the result is negative. The ADD MONTHS function adds to a particular date the number of calendar months you specify. You need to supply a whole number for the number of months you want to add. You can specify a negative number.

The syntax for ADD MONTHS is shown. The n in the syntax is the number of calendar months that you want to add to the date. The value for n must be an integer, and it may be negative. The NEXT DAY function identifies the date of the next specified day of the week. The syntax is as shown here. Char in the syntax may be a number representing a day, or a character string.

The days are numbered one to seven, starting with Sunday. The LAST DAY function identifies the date of the last day of the specified month. The syntax for LAST DAY is shown. The ROUND date function returns the date rounded to the nearest date of the unit you specify. For example the month or year. If you do not specify a unit, the date is rounded to the nearest day.

The syntax for ROUND is shown. The fit in the syntax is used to represent the format you want to round the date to. The TRUNC function returns the date, truncated to the nearest date of the unit you specify. If you do not specify a unit, the date is truncated to the nearest day. The syntax for TRUNC is shown here. The ROUND and TRUNC date functions operate in a similar manner to their number function namesakes.

Let's take a look at some examples of date functions. Suppose employees are reviewed annually, based on their hire date. You can use date functions to calculate the next review date for employees. To simplify the SELECT statements to follow, let's create a substitution variable review that calculates the review date. For more information on substitution variables, see the CBT Systems course 'Oracle Introduction: Sub queries, Reports, and DML'. Let's say you want to find the first Friday after the review date.

You use the NEXT DAY function to do this. And you can display the last day of the month in which the review date occurs using the LAST DAY date function. Let's say you want to search for the date six months after the review date. You use the ADD MONTHS function to do this. And you can use the MONTHS BETWEEN function to find the number of months between today and the review date for this year. If the review date is later than today, the value is negative.

If it's earlier, it's positive. If the current date is 17-Jun-98, and you want to round the current date to the nearest month, you can use this command. And to truncate the current date you can use this command. Here you can compare the effects of TRUNC and ROUND with the current date.