Archive for December, 2008

Tanggal pada Oracle

December 24, 2008

Singkat aja,

Untuk mendapatkan tanggal (termasuk jam nya) server sekarang, bisa menggunakan perintah sysdate.

select sysdate from dual;

Untuk menampilkan tanggal 1 januari gunakan perintah trunc() dengan format YEAR.

select trunc(sysdate, ‘YEAR’);

Untuk menampilkan tanggal awal bulan gunakan perintah trunc() dengan format MONTH.

select trunc(sysdate, ‘MONTH’);

Untuk menampilkan tanggal sesuai dengan format yang diinginkan gunakan perintah to_char().

select to_char(sysdate, ‘dd-mm-yyyy hh:mi:ss’);

Lebih jelasnya seperti terlihat di tabel berikut:

(Artikel disalin dari situs : http://www.oradev.com/oracle_date_format.jsp)

Oracle date format

With the functions to_char and to_date, a date format can be used. Example:
select to_char(sysdate,’DD/MM/YYYY HH24:MI:SS’) from dual;
will return something like: 24/03/2006 14:36:43

Here is a list of all the formats that can be used:

Format mask Description
CC Century
SCC Century BC prefixed with –
YYYY Year with 4 numbers
SYYY Year BC prefixed with –
IYYY ISO Year with 4 numbers
YY Year with 2 numbers
RR Year with 2 numbers with Y2k compatibility
YEAR Year in characters
SYEAR Year in characters, BC prefixed with –
BC BC/AD Indicator *
Q Quarter in numbers (1,2,3,4)
MM Month of year 01, 02…12
MONTH Month in characters (i.e. January)
MON JAN, FEB
WW Weeknumber (i.e. 1)
W Weeknumber of the month (i.e. 5)
IW Weeknumber of the year in ISO standard.
DDD Day of year in numbers (i.e. 365)
DD Day of the month in numbers (i.e. 28)
D Day of week in numbers(i.e. 7)
DAY Day of the week in characters (i.e. Monday)
FMDAY Day of the week in characters (i.e. Monday)
DY Day of the week in short character description (i.e. SUN)
J Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH Hournumber of the day (1-12)
HH12 Hournumber of the day (1-12)
HH24 Hournumber of the day with 24Hours notation (1-24)
AM AM or PM
PM AM or PM
MI Number of minutes (i.e. 59)
SS Number of seconds (i.e. 59)
SSSSS Number of seconds this day.