SQL Datetime Fonksiyonlari
1.Sistem saatini alan fonksiyon
SQL Server (transact sql)
GETDATE()
Mysql
NOW()
Oracle
SELECT SYSDATE FROM DUAL;
2. Datetime Fonksiyonlari (string ten datetime a cevir)
Table Test
id DateTimeOfTxn
1 2011-01-27 10:38:43.303
2 2011-01-27 10:38:43.303
SQL Server (transact sql)
select CAST(‘2007-05-08 12:35:29.123’ AS datetime) AS ‘datetime’ from test
2011-01-27 10:38:43.303
select cast(FLOOR(cast(AETT.DateTimeOfTxn as float)) as datetime) from test
2011-01-27 00:00:00.000
Mysql
STR_TO_DATE(str,format)
select str_to_date(DateTimeofTxn,’%m/%d/%Y’) from test
| Specifier | Description |
| %a | Abbreviated weekday name (Sun..Sat) |
| %b | Abbreviated month name (Jan..Dec) |
| %c | Month, numeric (0..12) |
| %D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, ?-) |
| %d | Day of the month, numeric (00..31) |
| %e | Day of the month, numeric (0..31) |
| %f | Microseconds (000000..999999) |
| %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 (January..December) |
| %m | Month, numeric (00..12) |
| %p | AM or PM |
| %r | Time, 12-hour (hh:mm:ss followed by 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 the week |
| %u | Week (00..53), where Monday is the first day of the week |
| %V | Week (01..53), where Sunday is the first day of the week; used with %X |
| %v | Week (01..53), where Monday is the first day of the week; used with %x |
| %W | Weekday name (Sunday..Saturday) |
| %w | Day of the week (0=Sunday..6=Saturday) |
| %X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
| %x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
| %Y | Year, numeric, four digits |
| %y | Year, numeric (two digits) |
| %% | A literal ‘%’ character |
| %x | x, for any ‘x’ not listed above |
ORACLE ( pl/sql)
select TO_DATE(DateTimeofTxn, ‘YYYY/MM/DD HH24:MI:SS’) from test;
SELECT TO_CHAR(SYSDATE, ‘DD-MON-YYYY HH:MI:SS’) FROM dual;
+1
SELECT SYSDATE + 1 FROM dual;
SELECT ROUND(TO_DATE(’27-OCT-00′),’YEAR’) NEW_YEAR FROM dual;
SELECT EXTRACT(YEAR FROM TO_DATE('01-JAN-2011 19:15:26','DD-MON-YYYY HH24:MI:SS')) AS YEAR FROM dual;
