Oracle built-in functions nvl, trunc

Nvl (string1, replace_with)

Function: If string1 is null, The nvl function returns the value of replace_with; otherwise, the value of string1 is returned.

This nvl serves the same purpose as isnull (string1, replace_with) in sqlserver.

Note: string1 and replace_with must be of the same data type unless the to_char function is displayed.

For example, nvl (field name, 0) indicates that when you select this field, although it is empty, it displays 0. Of course, this field can be changed to other items, such: 1, 2, 3 ......

 

 

TruncIs an oracle built-in function, you canDateType data is tailored to meet different types of data requirements.

Different format signs can be used to indicate different truncation locations and obtain various zero-moment time values.

 

	Format String	Description
Year	Yyyy or year	First day of the year (, January 1, January 1)
Month	Mm or month	The first day of the month (zero hour per day)
Day	Dd Or day	Zero date (00:00:00)
Hours	HH or hh24	Total time (XX: 00: 00)
Minute	Mi	Fraction (XX: 00)

 

 

 

 

 

 

 

 

Quarterly Q

Zhou ww

 

 

// Truncation to Year

SQL> select trunc (sysdate, 'yyyy') from dual;

 

Trunc (sysdate, 'yyyy ')

---------------------

2010-1-1

// Truncate to month

SQL> select trunc (sysdate, 'mm') from dual;

 

Trunc (sysdate, 'mm ')

-------------------

2010-12-1