Perbedaan Date dan Timestamp Pada beberapa database

 

Format standar DATE adalah yyyy-mm-dd sedangkan TIMESTAMP memiliki format standar yyyy-mm-dd hh:mm:ss.Pada masing-masing database memiliki System Date dan Query format yang berbeda-beda. Misalnya :

 

1. Untuk memanggil tanggal sekarang pada database oracle digunakan sintax :
   SELECT SYSDATE FROM DUAL dan hasilnya –> 29-APR-10
   jika ingin menampilkan format tanggal indonesia gunakan syntax :
   SELECT to_char (SYSDATE,' fmDay,DDMonth YYYY' , 'NLS_DATE_LANGUAGE=Indonesian') hari_ini FROM DUAL
   Maka hasilnya : –>  Kamis, 29 April 2010
2. Untuk memanggil tanggal sekarang pada database MySQL digunakan sintax SELECT NOW() as DATE maka hasilnya –> 2010-04-29 13:42:42
   select date(current_timestamp()) as Date hasilnya --> 2010-04-29

 

Berikut ini perbandingan system format dan Query format yang saya ambil dari http://www.analysisandsolutions.com/code/dates.htm

 

TIMESTAMP

DBMS	Data Type	System Format	Query Format
SQL:1999	TIMESTAMP	n/a	n/a
DB2 8.1	TIMESTAMP	n/a	TO_CHAR(col, 'YYYY-MM-DD HH24:MI:SS')
FrontBase 4.1	TIMESTAMP	n/a	n/a
Interbase 7.1	TIMESTAMP	SET SQL DIALECT 3; This is the default in 7.1	n/a
MySQL 3.23, 4.0	DATETIME	n/a	n/a
MySQL 3.23, 4.0	TIMESTAMP	n/a	DATE_FORMAT (col, '%Y-%m-%d %T') Not necessary in MySQL >= 4.1
Oracle 9.2	DATE	ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; Required for input	TO_CHAR (col, 'YYYY-MM-DD HH24:MI:SS') Not needed if NLS_DATE_FORMAT set
Oracle 9.2	DATE	ALTER SESSION SET NLS_DATE_FORMAT = 'SYYYY-MM-DD HH24:MI:SS'; Required for input The "S" format element extends the range of usable dates by prefixing BC dates with "-"	TO_CHAR (col, 'SYYYY-MM-DD HH24:MI:SS') Not needed if NLS_DATE_FORMAT set
Oracle 9.2	TIMESTAMP(0) Introduced in 9i	ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'SYYYY-MM-DD HH24:MI:SS'; Required for input The "S" format element extends the range of usable dates by prefixing BC dates with "-"	TO_CHAR (col, 'SYYYY-MM-DD HH24:MI:SS') Not needed if NLS_TIMESTAMP_FORMAT set
PostgreSQL 7.4	TIMESTAMP(0)	SET DATESTYLE = 'ISO'; This is the default in 7.4	TO_CHAR (col, 'YYYY-MM-DD HH24:MI:SS') Not needed if DATESTYLE is ISO
PostgreSQL 7.4	TIMESTAMP(0)	SET DATESTYLE = 'ISO'; This is the default in 7.4	TO_CHAR (col, 'YYYY-MM-DD HH24:MI:SS BC') Not needed if DATESTYLE is ISO
SQL Server 2000	DATETIME	SET DATEFORMAT ymd; Ensures interpretation of input	CONVERT (CHAR(19), col, 120)
Sybase ASE 12.5.1	DATETIME	SET DATEFORMAT ymd; Ensures interpretation of input	STR_REPLACE( CONVERT( CHAR(10), col, 102), '.', '-') + ' ' + CONVERT( CHAR(8), col, 20)

 

DATE

 

DBMS	Data Type	System Format	Query Format
SQL:1999	DATE	n/a	n/a
DB2 8.1	DATE	n/a	n/a
FrontBase 4.1	DATE	n/a	n/a
Interbase 7.1	DATE	SET SQL DIALECT 3; This is the default in 7.1	n/a
MySQL 3.23, 4.0	DATE	n/a	n/a
Oracle 9.2	DATE	ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';	TO_CHAR (col, 'YYYY-MM-DD')
Oracle 9.2	DATE	ALTER SESSION SET NLS_DATE_FORMAT = 'SYYYY-MM-DD'; Required for input The "S" format element extends the range of usable dates by prefixing BC dates with "-"	TO_CHAR (col, 'SYYYY-MM-DD') Not needed if NLS_DATE_FORMAT set
PostgreSQL 7.4	DATE	SET DATESTYLE = 'ISO'; This is the default in 7.4	TO_CHAR (col, 'YYYY-MM-DD') Not needed if DATESTYLE is ISO
PostgreSQL 7.4	DATE	SET DATESTYLE = 'ISO'; This is the default in 7.4	TO_CHAR (col, 'YYYY-MM-DD BC') Not needed if DATESTYLE is ISO
SQL Server 2000	DATETIME	SET DATEFORMAT ymd; Ensures interpretation of input	CONVERT (CHAR(10), col, 120)
SQL Server 2000	SMALLDATETIME	SET DATEFORMAT ymd; Ensures interpretation of input	CONVERT (CHAR(10), col, 120)
Sybase ASE 12.5.1	DATE	SET DATEFORMAT ymd; Ensures interpretation of input	STR_REPLACE( CONVERT( CHAR(10), col, 102), '.', '-')
Sybase ASE 12.5.1	SMALLDATETIME	SET DATEFORMAT ymd; Ensures interpretation of input	STR_REPLACE( CONVERT( CHAR(10), col, 102), '.', '-')

Baca Juga

Cari yang lain: