Oracle/PLSQL: To_Date Function

In Oracle/PLSQL, the to_date function converts a string to a date.

The syntax for the to_date function is:

to_date( string1, [ format_mask ], [ nls_language ] )

string1 is the string that will be converted to a date.

format_mask is optional. This is the format that will be used to convert string1 to a date.

nls_language is optional. This is the nls language used to convert string1 to a date.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
RRRR	Accepts a 2-digit year and returns a 4-digit year. A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year.
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM, A.M., PM, or P.M.	Meridian indicator
AD or A.D	AD indicator
BC or B.C.	BC indicator
TZD	Daylight savings information. For example, 'PST'
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

to_date('2003/07/09', 'yyyy/mm/dd')	would return a date value of July 9, 2003.
to_date('070903', 'MMDDYY')	would return a date value of July 9, 2003.
to_date('20020315', 'yyyymmdd')	would return a date value of Mar 15, 2002.

Oracle/PLSQL: Length Function

In Oracle/PLSQL, the length function returns the length of the specified string.

The syntax for the length function is:

length( string1 )

string1 is the string to return the length for. If string1 is NULL, then the function returns NULL.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

length(NULL)	would return NULL.
length('')	would return NULL.
length('Tech on the Net')	would return 15.
length('Tech on the Net ')	would return 16.

Oracle/PLSQL: Concat Function

In Oracle/PLSQL, the concat function allows you to concatenate two strings together.

The syntax for the concat function is:

concat( string1, string2 )

string1 is the first string to concatenate.

string2 is the second string to concatenate.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

concat('Tech on', ' the Net');	would return 'Tech on the Net'.
concat('a', 'b')	would return 'ab'.

Creating a simple search in rows

Programmer usually needs creates searching data after retrieving datawindow. Here is simple searching data between rows.

long ll_nbr, ll_foundrow
string is_nostaf

ll_nbr = dw_1.rowcount( )
is_nostaf = trim(sle_name.text)
ll_foundrow = dw_1.Find("px01staf_px01numstaf = '"+ is_nostaf +"'", 1, ll_nbr)

if ll_foundrow > 0 then
dw_1.scrolltorow(ll_foundrow)
dw_1.selectrow( 0, false)
dw_1.selectrow( ll_foundrow, true)
else
messagebox('Mesej','No data')
end if

How to change color between different rows

Sometimes creating datawindow, programmer needs changes colors between rows. Usually it use for makes a different status, maybe student active in blue colors and not active in grey colors.
At datawindow, click details an insert color format like this. Format like this if(b,t,f) in other word if (boolean,true,false).

(student_status = 'A', RGB(153,204,204), RGB(153,153,153))

How to find duplicate records in a database

Often a developer finds it necessary to deal with duplicate data in a database. They may have a need to provide a list of duplicate records or delete the duplicate records. The below methods provide an easy approach to working with duplicate data.

To create a list of the duplicate records with a count of the number of duplicates per record:

SELECT column, COUNT( column )
FROM table
GROUP BY column
HAVING count( column ) > 1

To create a list of ALL duplicate records, where there will be a listing for EACH record:

SELECT column
FROM TABLE
WHERE column in (SELECT column FROM table
HAVING count(column) > 1 )
ORDER BY column