ls Command

• The ls is the most widely used command in unix or linux.
• The ls command is a command-line utility for listing the contents of a directory or directories given to it via standard input.

CAST Function

CAST function converts one built-in data type into another built-in data type with the specified precision and length.

Syntax: CAST ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

CAST Converts

Convert a string value to NUMBER leaving 2 digits after the decimal point:

SELECT CAST('345.213' AS NUMBER(5,2)) FROM DUAL;

CAST - Convert and Round Numbers:

SELECT CAST('246.206' AS NUMBER(5,2)) FROM DUAL;

CAST convert the date into a VARCHAR2(30) value:

 SELECT CAST( '22-Aug-2003' AS VARCHAR2(30) ) FROM DUAL;

TRUNC for Datetime

TO_DATE

TO_CHAR for Datetime

MONTHS_BETWEEN

MONTHS_BETWEEN function returns the number of months between two dates.

Syntax: MONTHS_BETWEEN(date_expression1,date_expression2);

Months Between Function Details

MONTHS_BETWEEN function returns an integer number if the days are the same.

SELECT MONTHS_BETWEEN (DATE '2012-02-12', DATE '2012-01-12') FROM DUAL; 

MONTHS_BETWEEN function returns an integer number if the both dates specify the last day of the month.

SELECT MONTHS_BETWEEN (DATE '2012-02-29', DATE '2012-01-31') FROM DUAL; 

MONTHS_BETWEEN function returns a decimal number if the days are different.

SELECT MONTHS_BETWEEN (DATE '2012-02-29', DATE '2012-02-01') FROM DUAL;

MONTHS_BETWEEN always calculates the fractional part as the difference in days divided by 31

In the example above, the fractional part is calculated as (29 - 1)/31 = 0.903225806, although there are 29 days in February 2012.

You can also see that MONTHS_BETWEEN does not return an integer result if you specify the first and last days of the same month.

EXTRACT Function

EXTRACT function gets the specified part (day, month, year, hours, minutes,second etc.) from a datetime value.

Syntax: EXTRACT(datetime_unit FROM datetime_expression1)

Although DATE contains time part but we can't extract the hour,minute and second from DATE.
HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only.

EXTRACT Function Details

Extract DAY from a specific DATE.

SELECT EXTRACT(DAY FROM DATE '2011-01-12') FROM dual;

Extract MONTH from a specific DATE.

SELECT EXTRACT(MONTH FROM DATE '2011-01-12') FROM dual;

Extract YEAR from a specific DATE.

SELECT EXTRACT(YEAR FROM DATE '2011-01-12') FROM dual;

We can't extract  hour,minute and second from DATE. We will get the error

SELECT EXTRACT(HOUR FROM SYSDATE) FROM dual;

HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only.

SELECT EXTRACT(HOUR FROM TIMESTAMP '2012-01-12 10:11:00') FROM dual;