Syntax of following SQL commands: sysdate, to date(), dual table ,to_number, substr() and initcap()

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.