[Database][Mysql/SQL Server/Oracle] Output Query format Date

Deskripsi
      Berikut adalah cara dalam mendapatkan format output date dari 3 database yakni mysql , oracle , dan Sqlserver, menyebalkan memang jika berhubungan dengan date pada saat query ~_~!.

Howto

Fundamental Datetime
     Pada dasarnya penyimpanan format date pada database normalnya memiliki format :
 - 'YYYY/MM/DD HH:MM:SS AM/PM' => Oracle
-  'MM/DD/YYYY HH:MM:SS AM/PM' => SQL Server
-  'YYYY/MM/DD HH:MM:SS'              => Mysql

nah bagaimana cara narik datanya berdasarkan date ?? berikut adalah contoh query dari masing-masing database.

1. Oracle
    to_date ('10/05/2013','dd/mm/yyyy');
 
example : select *
               from table
              where creation_date > to_date ('10/05/2013','dd/mm/yyyy');


2. SQL Server
   CONVERT(type,field or date,code_format)

here's the code_format (source from w3schools)

Value (century yy)	Value (century yyyy)	Input/Output	Standard
-	0 or 100	mon dd yyyy hh:miAM (or PM)	Default
1	101	mm/dd/yy	USA
2	102	yy.mm.dd	ANSI
3	103	dd/mm/yy	British/French
4	104	dd.mm.yy	German
5	105	dd-mm-yy	Italian
6	106	dd mon yy
7	107	Mon dd, yy
8	108	hh:mm:ss
-	9 or 109	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Default+millisec
10	110	mm-dd-yy	USA
11	111	yy/mm/dd	Japan
12	112	yymmdd	ISO
-	13 or 113	dd mon yyyy hh:mi:ss:mmm (24h)
14	114	hh:mi:ss:mmm (24h)
-	20 or 120	yyyy-mm-dd hh:mi:ss (24h)
-	21 or 121	yyyy-mm-dd hh:mi:ss.mmm (24h)
-	126	yyyy-mm-ddThh:mi:ss.mmm (no spaces)	ISO8601
-	130	dd mon yyyy hh:mi:ss:mmmAM	Hijiri
-	131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri

Example:
             select  CONVERT(VARCHAR,create_date,103) as create_date
    from MS_CLIENT_H
             where create_date > '2013-02-01 14:26:40'

3. Mysql
    Date_Format(date,format)
 
here's the code_format (source from w3schools)

Format	Description
%a	Abbreviated weekday name
%b	Abbreviated month name
%c	Month, numeric
%D	Day of month with English suffix
%d	Day of month, numeric (00-31)
%e	Day of month, numeric (0-31)
%f	Microseconds
%H	Hour (00-23)
%h	Hour (01-12)
%I	Hour (01-12)
%i	Minutes, numeric (00-59)
%j	Day of year (001-366)
%k	Hour (0-23)
%l	Hour (1-12)
%M	Month name
%m	Month, numeric (00-12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss AM or PM)
%S	Seconds (00-59)
%s	Seconds (00-59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00-53) where Sunday is the first day of week
%u	Week (00-53) where Monday is the first day of week
%V	Week (01-53) where Sunday is the first day of week, used with %X
%v	Week (01-53) where Monday is the first day of week, used with %x
%W	Weekday name
%w	Day of the week (0=Sunday, 6=Saturday)
%X	Year of the week where Sunday is the first day of week, four digits, used with %V
%x	Year of the week where Monday is the first day of week, four digits, used with %v
%Y	Year, four digits
%y	Year, two digits

Example :
     SELECT datecolumn
     FROM `user`
     WHERE datecolumn = date_format('23-05-2013','%d-%m-%y')



Hope this helps :D