1. SYSDATE
Purpose: SYSDATE is a function that returns the current date and time of the database server.
Syntax
SELECT SYSDATE FROM DUAL;Example Use: It’s often used to get the current date and time for record keeping, such as logging when a transaction occurred.
2. TO_DATE()
Purpose: TO_DATE() is a function used to convert a string into a date format based on the specified format model.rmat_model)
Syntax
TO_DATE(string, format_model)Example Use
SELECT TO_DATE('2024-03-25', 'YYYY-MM-DD') FROM DUAL;3. DUAL Table
Purpose: DUAL is a special one-row, one-column table present by default in Oracle and some other database systems. It’s primarily used for selecting a pseudo column like SYSDATE or performing calculations that don’t require data from a specific table.
Syntax
SELECT <expression> FROM DUAL;Example Use
SELECT SYSDATE FROM DUAL;4. TO_NUMBER
Purpose: TO_NUMBER() function converts a string to a number, using the specified format model for the conversion if provided.
Syntax
TO_NUMBER(string, [format_model])string is the text string to convert to a number.
format_model (optional) specifies the format of the string.
Example Use
SELECT TO_NUMBER('123.456', '999.999') FROM DUAL;5. SUBSTR()
Purpose: SUBSTR() function is used to extract a substring from a string, starting at a specified position, and for a specified length.
Syntax
SUBSTR(string, start_position, [length])string is the source string.
start_position indicates the starting point (Note: the first position in the string is 1).
length (optional) is the number of characters to extract. If omitted, it extracts till the end of the string.
Example Use
SELECT SUBSTR('Hello World', 1, 5) FROM DUAL;6. INITCAP()
Purpose: INITCAP() function converts the first character of each word in a provided string to uppercase and the rest to lowercase.
Syntax
INITCAP(string)Example Use
SELECT INITCAP('hello world') FROM DUAL;These functions are essential in SQL for data manipulation, conversion, and retrieval, providing flexibility in how data is processed and presented.