1. ¹®ÀÚ ½Ä¿¡¼ °¡Àå ¿ÞÂÊ ¹®ÀÚÀÇ ASCII ÄÚµå °ª ¹Ýȯ (¼ýÀڷΠǥ½ÃµÊ)
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
ASCII
|
ASCII
|
¿ë·Ê
|
SELECT ASCII('A')
|
SELECT ASCII('A') FROM DUAL
|
°á°ú
|
65
|
65
|
è MSSQLÀ̵ç ORacleÀÌµç ‘’ ¾È¿¡ ¿©·¯ °³ÀÇ ¹®ÀÚ°¡ À־ °¡Àå ÁÂÃø °ª¸¸ ¹ÝȯÇÕ´Ï´Ù.
Áï, SELECT ASCII(‘A’) ³ª SELECT ASCII(‘ABCDEFG’) ³ª °á°ú´Â °°½À´Ï´Ù.
2. ¹®ÀÚ ÇÕÄ¡±â
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
+
|
CONCAT ¶Ç´Â ||
|
¿ë·Ê
|
SELECT 'µ¿Çع°°ú' + '¹éµÎ»êÀÌ'
|
1. SELECT 'µ¿Çع°°ú ' || '¹éµÎ»êÀÌ' FROM DUAL
2. SELECT CONCAT('µ¿Çع°°ú ','¹éµÎ»êÀÌ') FROM DUAL
|
°á°ú
|
µ¿Çع°°ú ¹éµÎ»êÀÌ
|
µ¿Çع°°ú ¹éµÎ»êÀÌ
|
3. ASCII Äڵ带 ¹®ÀÚ·Î º¯È¯Çϱâ
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
CHAR
|
CHR
|
¿ë·Ê
|
SELECT CHAR(67)
|
SELECT CHR(67) FROM DUAL
|
°á°ú
|
C
|
C
|
è Âü°í·Î 9¹øÀº TAB, 10Àº LF(Line Feed), 13¹øÀº CR(Carriage Return) ÀÔ´Ï´Ù.
4. ÁÂÃø¿¡¼ ¸î ¹ø°¿¡ ÇØ´ç ¹®ÀÚ°¡ ÀÖ´ÂÁö ¾Ë·ÁÁÖ±â
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
CHARINDEX
|
INSTR
|
¿ë·Ê
|
SELECT CHARINDEX('¸¶ÀÌ', '°í¸¶Çضó. ¸¶ÀÌ ¹«¿ìµû ¾ÆÀÌ°¡?')
|
SELECT INSTR('°í¸¶Çضó. ¸¶ÀÌ ¹«¿ìµû ¾ÆÀÌ°¡?','¸¶ÀÌ' ) FROM DUAL
|
°á°ú
|
7
|
7
|
è MSSQL°ú OracleÀÇ ÇÔ¼ö»ç¿ë ¼ø¼°¡ ´Ù¸¨´Ï´Ù.
è MSSQL¿¡´Â ÆÐÅÏã±â¿¡ PATINDEX¸¦ ¸¹ÀÌ »ç¿ëÇÕ´Ï´Ù. À§¿Í °°Àº °á°ú¸¦ ¾òÀ¸·Á¸é
SELECT PATINDEX('%¸¶ÀÌ%', '°í¸¶Çضó. ¸¶ÀÌ ¹«¿ìµû ¾ÆÀÌ°¡?')
ó·³ »ç¿ëÇÏ¸é µË´Ï´Ù.(¿ÍÀϵå Ä«µå »ç¿ë °¡´É)
5. ´ë¹®ÀÚ º¯È¯, ¼Ò¹®ÀÚ º¯È¯
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
UPPER / LOWER
|
UPPER / LOWER
|
¿ë·Ê
|
SELECT UPPER('aBcDeF'),LOWER('aBcDeF')
|
SELECT UPPER('aBcDeF'), LOWER('aBcDeF') FROM DUAL
|
°á°ú
|
ABCDEF abcdef
|
ABCDEF abcdef
|
6. ÁÂÃø°ø°£À» ƯÁ¤ ¹®ÀڷΠä¿öÁÖ±â
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
Áö¿øÇÏÁö ¾ÊÀ½(¾øÀ½)
|
LPAD
|
¿ë·Ê
|
-
|
SELECT LPAD('13579',10, '0') FROM DUAL
|
°á°ú
|
-
|
0000013579
|
è MSSQL¿¡¼± ¾ø´Â ÇÔ¼öÀ̱⠶§¹®¿¡ ¾Æ·¡¿Í °°ÀÌ »ç¿ëÀÚÇÔ¼ö¸¦ ¸¸µé¾î¼ »ç¿ëÇϱ⵵ ÇÕ´Ï´Ù.
-- 1. FUNCTION ¸¸µé±â
CREATE FUNCTION dbo.UFN_LPAD
(
@INPUT VARCHAR(8000),
@COUNT AS INT,
@FILLCHAR AS CHAR(1)=' '
)
RETURNS varchar(200)
AS
BEGIN
RETURN
CASE
WHEN LEN(@INPUT) >= @COUNT THEN LEFT(@INPUT, @COUNT)
ELSE
LEFT(REPLICATE(@FILLCHAR, @COUNT), @COUNT-LEN(@INPUT)) + @INPUT
END
END
-- 2. SAMPLE
SELECT dbo.UFN_LPAD('12',10,'0') AS PR_KEY
--> °á°ú
0000000012
|
7. ¿ìÃø°ø°£À» ƯÁ¤ ¹®ÀڷΠä¿öÁÖ±â
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
Áö¿øÇÏÁö¾ÊÀ½(¾øÀ½)
|
RPAD
|
¿ë·Ê
|
-
|
SELECT RPAD('13579',10, '0') FROM DUAL
|
°á°ú
|
-
|
1357900000
|
è MSSQL¿¡¼± ¾ø´Â ÇÔ¼öÀ̱⠶§¹®¿¡ UFN_LPADó·³ ÇÔ¼ö¸¦ ¸¸µé¾î¼ ¾¹´Ï´Ù.
´Ù¸¸, REPLICATE¶ó´Â ÇÔ¼ö°¡ Àִµ¥, ÀÌ°ÍÀº ƯÁ¤¹®ÀÚ¸¦ ¿¬¼ÓÀûÀ¸·Î ä¿ö ÁÙ »Ó, RPAD¿Í´Â Á¶±Ý ´Ù¸¨´Ï´Ù.
-- »ç¿ë·Ê
SELECT REPLICATE('0',10)
--> °á°ú
0000000000
|
¶ÇÇÑ, SPACE¶ó´Â ÇÔ¼ö´Â °ø¹é¸¸ ä¿öÁÝ´Ï´Ù.
-- »ç¿ë·Ê
SELECT '³ªÀÇ' + SPACE(10) + '°Í'
--> °á°ú
³ªÀÇ °Í
|
8. ÁÂ/¿ì °ø¹é ¾ø¾ÖÁÖ±â
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
LTRIM / RTRIM
|
LTRIM / RTRIM
|
¿ë·Ê
|
SELECT LTRIM(' ¾Æ¹öÁö'), RTRIM('¾î¸Ó´Ï ')
|
SELECT LTRIM(' ¾Æ¹öÁö'), RTRIM('¾î¸Ó´Ï ') FROM DUAL
|
°á°ú
|
¾Æ¹öÁö ¾î¸Ó´Ï à (°ø¹éÁ¦°ÅµÊ)
|
¾Æ¹öÁö ¾î¸Ó´Ï à (°ø¹éÁ¦°ÅµÊ)
|
9. ¹®ÀÚÀÇ À½¼ºÇ¥ÇöÀ» °¡Áö´Â ¹®ÀÚ¿À» ¹Ýȯ. ±¹³»¿¡¼´Â ÈçÈ÷ »ç¿ëÇÏÁö ¾ÊÀ½(ÇÑ±Û µ¿ÀÛ ¾ÈÇÔ)
b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
l = 4
m, n = 5
r = 6
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
SOUNDEX
|
SOUNDEX
|
¿ë·Ê
|
SELECT SOUNDEX ('Smith'), SOUNDEX('Smythe');
|
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') FROM DUAL
|
°á°ú
|
S252 S200
|
S530 S530
|
è ¾î¶ó? ¿¹Á¦¿¡´Â °°Àº °ÍÀ¸·Î µÇ¾î Àִµ¥, ½ÇÁ¦ °á°ú´Â ´Ù¸£³×¿ä. -_- ÀÌ°Í Âü…
10. Àüü ¹®ÀÚ¿¿¡¼ ÁöÁ¤ÇÑ ±æÀ̸¸ÅÀÇ ¹®ÀÚ¿ ¹Ýȯ
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
SUBSTRING
|
SUBSTR
|
¿ë·Ê
|
SELECT SUBSTRING('1234567890',4,5)
|
SELECT SUBSTR('1234567890',4,5) FROM DUAL
|
°á°ú
|
45678
|
45678
|
è 4¹ø° ÀÚ¸®¿¡¼ ½ÃÀÛÇؼ ´Ù¼¸ ±ÛÀÚ¸¦ °¡Á®¿É´Ï´Ù.
11. ¹®ÀÚ¿ º¯È¯
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
REPLACE
|
REPLACE
|
¿ë·Ê
|
SELECT REPLACE('1234567','123','321')
|
SELECT REPLACE('1234567','123','321') FROM DUAL
|
°á°ú
|
3214567
|
3214567
|
è MSSQLÀÇ STUFF¶ó´Â ÇÔ¼ö°¡ Àִµ¥¿ä. ÀÌ°ÍÀº ¿¢¼¿ÀÇ REPLACE¿Í °°½À´Ï´Ù.
SELECT STUFF('13579',2,3,'222')
--> °á°ú
12229
|
12. À½ÀýÀÇ Ã¹ ±ÛÀÚ¸¸ ´ë¹®ÀÚ·Î º¯È¯(Pascal Case)
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
Áö¿øÇÏÁö ¾ÊÀ½
|
INITCAP
|
¿ë·Ê
|
-
|
SELECT INITCAP('we are the world') FROM DUAL;
|
°á°ú
|
-
|
We Are The World
|
11. ¹®ÀÚ¿¿¡¼ ƯÁ¤ ¹®ÀÚ¸¸ ½ï½ï °ñ¶ó¼ ´Ù¸¥ ¹®ÀÚ·Î º¯È¯, Á¤È®ÇÏ°Ô À̾߱âÇϸé ÁöÁ¤ÇÑ ¹®ÀÚ¿Í ¸ÂÀ¸¸é ƯÁ¤ ¹®ÀڷΠó¸®ÇÔ,¶Ç´Â ƯÁ¤ ÁöÁ¤¹®ÀÚ°¡ ¾ø´Â °æ¿ì ÇØ´ç ¹®ÀÚ¸¦ »èÁ¦ÇÔ.
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
Áö¿øÇÏÁö ¾ÊÀ½
|
TRANSLATE
|
¿ë·Ê
|
-
|
SELECT TRANSLATE('NothingToUse','o','!') FROM DUAL;
SELECT TRANSLATE('BFG123', '12345BCDEFG', '123XXXXXXXX') FROM DUAL;
|
°á°ú
|
-
|
N!thingT!Use
XXX123
|
è ¾Æ·¡ SAMPLEÀ» ÂüÁ¶Çϼ¼¿ä.
create table translate_test
(
mem_id varchar(20)
)
;
INSERT INTO translate_test (mem_id) values ('ABCD1234');
INSERT INTO translate_test (mem_id) values ('abcdef');
INSERT INTO translate_test (mem_id) values ('585472');
COMMIT;
select mem_id, translate(mem_id, '0123456789' || mem_id, '0123456789')
from translate_test;
-- °á°ú
ABCD1234 1234
abcdef
585472 585472
|
14. ³ª¿ÇÑ Àμö Áß °¡Àå Å« °ª / ÀÛÀº °ªÀ» ¹Ýȯ
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
Áö¿øÇÏÁö ¾ÊÀ½
|
GREATEST / LEAST
|
¿ë·Ê
|
-
|
SELECT GREATEST('Z9', '³ªÀÇ','ÄÝ','A341', '999','123') FROM DUAL;
SELECT LEAST('Z9', '³ªÀÇ','ÄÝ','A341', '999','123') FROM DUAL;
|
°á°ú
|
-
|
ÄÝ / 123
|
è ºñ±³ÇÏ´Â °ÍÀº, ¹®ÀÚ¼øÀ§(Aº¸´Ù´Â Z°¡ Å©´Ù. ÇѱÛÀº ÀÚ¸ð¼ø¼¿¡ ÀÇÇÔ), ¼ýÀÚ¼øÀ§(0º¸´Ù´Â 9°¡ Å©°¡), ±ÛÀÚ¼ö´ÜÀ§(°°Àº ¹®ÀÚ·Î ½ÃÀÛÇÏ´õ¶óµµ °á±¹ ±ÛÀÚ¼ö°¡ ¸¹Àº °ÍÀÌ Å©´Ù.)
15. ±æÀÌ °¡Á®¿À±â, ¶Ç´Â BYTE ´ÜÀ§ ±æÀÌ °¡Á®¿À±â
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
LEN, DATALENGTH
|
LENGTH
|
¿ë·Ê
|
SELECT LEN('NothingToUse')
SELECT DATALENGTH('NothingToUse')
SELECT LEN('Áö¿øÇÏÁö ¾ÊÀ½')
SELECT DATALENGTH('Áö¿øÇÏÁö ¾ÊÀ½')
|
SELECT LENGTH('NothingToUse') FROM DUAL;
SELECT LENGTH('Áö¿øÇÏÁö ¾ÊÀ½') FROM DUAL;
|
°á°ú
|
12 / 12 / 7 / 13
|
12 / 7
|
è À§¿¡¼ º¸½Ã¸é ¾Ë°ÚÁö¸¸, BYTE ´ÜÀ§·Î ±æÀ̸¦ È®ÀÎÇϽ÷Á¸é LEN ÀÌ ¾Æ´Ñ DATALENGTH ¸¦ »ç¿ëÇÏ¼Å¾ß ÇÕ´Ï´Ù.
16. NULL ÀÏ °æ¿ì ´ëü°ª Ç¥½Ã.
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
ISNULL
|
NVL
|
¿ë·Ê
|
SELECT ISNULL(QTY1,100) FROM A_TEMP
|
SELECT NVL(QTY1,100) FROM A_TEMP;
|
°á°ú
|
100 (°ªÀÌ NULLÀÏ °æ¿ì)
|
100 (°ªÀÌ NULL ÀÏ °æ¿ì)
|
17. ¼ýÀÚÇüÀ» ¹®ÀÚÇüÀ¸·Î º¯È¯
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
STR, CONVERT, CAST
|
TO_CHAR
|
¿ë·Ê
|
SELECT 123 + 456
SELECT STR(123) + STR(456)
SELECT STR(123,3,0) + STR(456,3,0)
|
SELECT TO_CHAR(123) || TO_CHAR(456) FROM DUAL;
SELECT 123 || 456 FROM DUAL;
|
°á°ú
|
579
123 456
123456
|
123456
123456
|
è MSSQLÀÇ STR´Â ±âº» ÀÚ¸´¼ö°¡ 10ÀÚ¸®ÀÔ´Ï´Ù.
è Oracle¿¡¼´Â À§¿¡¼ º¸½Ã´Ù½ÃÇÇ ¼ýÀÚÇüŸ¦ ConcatÇÏ´õ¶óµµ ÀÚµ¿ÀûÀ¸·Î ¹®ÀÚ·Î ³ª¿É´Ï´Ù.
è ¹°·Ð MSSQL¿¡¼´Â CONVERT³ª CAST¸¦ ÈξÀ ´õ ¸¹ÀÌ »ç¿ëÇÕ´Ï´Ù.
SELECT CONVERT(VARCHAR(3),123) + CONVERT(VARCHAR(3),456)
--> °á°ú
123456
|
18. ¹®ÀÚÇüÀ» ¼ýÀÚÇüÀ¸·Î º¯È¯
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
CONVERT, CAST
|
TO_NUMBER
|
¿ë·Ê
|
SELECT '123' + '456'
SELECT CONVERT(INT,'123') + CONVERT(INT,'456')
SELECT CAST('123' AS INT) + CAST('456' AS INT)
|
SELECT TO_NUMBER('123') + TO_NUMBER('456') FROM DUAL;
|
°á°ú
|
123456 / 579 / 579
|
579
|
19. Á¶°Çó¸® ±¸¹®
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
CASE
|
CASE (DECODE)
|
è MSSQL CASE¹®Àº ¿©·¯ °³ÀÇ Á¶°Ç Áß ¸Â´Â °á°ú¸¦ Ç¥½ÃÇÏ´Â °ÍÀÌ°í, OracleÀÇ DECODE´Â Âü/°ÅÁþ¿¡ µû¶ó °á°ú¸¦ Ç¥½ÃÇÕ´Ï´Ù.
1) MSSQL CASE SAMPLE
-- 1. Å×À̺í»ý ¼º
CREATE TABLE CASE_STUDY
(PKEY INT PRIMARY KEY,
DATA1 VARCHAR(20),
DATA2 VARCHAR(30)
);
-- 2. ÀÚ·á ÀÔ·Â
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (1, 'A', '°¡');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (2, 'B', '³ª');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (3, 'C', '´Ù');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (4, 'D', '¶ó');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (5, 'E', '¸¶');
-- 3. µ¥ÀÌÅÍ Ãâ·Â
SELECT
PKEY,
CASE
WHEN PKEY = 1 THEN DATA1
WHEN PKEY = 2 THEN DATA2
WHEN PKEY = 3 THEN DATA1
ELSE DATA2
END AS DATA
FROM
CASE_STUDY
;
-- 4. °á°ú
1 A -- 1ÀÏ ¶§ DATA1°ª
2 ³ª -- 2ÀÏ ¶§ DATA2°ª
3 C -- 3ÀÏ ¶§ DATA1°ª
4 ¶ó -- ¾Æ´Ï¸é DATA2°ª
5 ¸¶ -- ¾Æ´Ï¸é DATA2°ª
|
2) °°Àº ÀڷḦ Oracle DECODE¸¦ »ç¿ëÇÒ °æ¿ì
-- 1. Å×À̺í»ý ¼º
CREATE TABLE CASE_STUDY
(
PKEY NUMBER(9),
DATA1 VARCHAR2(20),
DATA2 VARCHAR2(30)
);
-- 2. ÀÚ·á ÀÔ·Â
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (1, 'A', '°¡');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (2, 'B', '³ª');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (3, 'C', '´Ù');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (4, 'D', '¶ó');
INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (5, 'E', '¸¶');
-- 3. µ¥ÀÌÅÍ Ãâ·Â
SELECT
PKEY,
DECODE(PKEY, 1, DATA1,
DECODE(PKEY, 2, DATA2,
DECODE(PKEY, 3, DATA1,
DATA2)
)
) AS DATA
FROM
CASE_STUDY
-- ¶Ç´Â
SELECT
PKEY,
DECODE(PKEY, 1, DATA1,
2, DATA2,
3, DATA1,
DATA2) AS DATA
FROM
CASE_STUDY
-- 4. °á°ú
1 A -- 1ÀÏ ¶§ DATA1°ª
2 ³ª -- 2ÀÏ ¶§ DATA2°ª
3 C -- 3ÀÏ ¶§ DATA1°ª
4 ¶ó -- ¾Æ´Ï¸é DATA2°ª
5 ¸¶ -- ¾Æ´Ï¸é DATA2°ª
|
è Oracle 8.1.7ºÎÅÍ´Â MSSQL°ú °ÅÀÇ µ¿ÀÏÇÑ CASE¹®À» Á¦°øÇÕ´Ï´Ù.
À§ ±¸¹®À» MSSQL ±¸¹®°ú µ¿ÀÏÇÏ°Ô Çϼŵµ °á°ú´Â °°½À´Ï´Ù.
20. Áö±Ý(Right Now) °¡Á®¿À±â
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
GETDATE()
|
SYSDATE
|
¿ë·Ê
|
SELECT GETDATE()
|
SELECT SYSDATE FROM DUAL;
|
°á°ú
|
2010-11-07 11:50:08.700
|
2010/11/07 11:50:09
|
è MSSQL 2008¿¡¼´Â “SYSDATETIME()” À» ÅëÇؼ ´õ »ó¼¼ÇÏ°Ô »ç¿ëÀÌ °¡´ÉÇÕ´Ï´Ù.(DATETIME2)
SELECT SYSDATETIME()
-- °á°ú
2010-11-07 11:52:25.9900000
|
21. ÀÏÀÚ ´õÇϱâ / »©±â
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
DATEADD
|
+ / -
|
¿ë·Ê
|
SELECT GETDATE();
SELECT DATEADD(d,1,GETDATE());
SELECT DATEADD(hh,5,DATEADD(d,1,GETDATE()));
SELECT DATEADD(d,-1,GETDATE());
|
SELECT SYSDATE FROM DUAL;
SELECT SYSDATE + 1 FROM DUAL;
SELECT SYSDATE + 1.5 FROM DUAL;
|
°á°ú
|
2010-11-07 11:57:38.140
2010-11-08 11:57:38.140
2010-11-08 16:57:38.140
2010-11-06 11:57:38.140
|
2010/11/07 11:58:09
2010/11/08 11:58:09
2010/11/08 23:58:09
2010/11/06 11:58:09
|
è À§ °á°ú¸¦ º¸½Ã¸é ¾Æ½Ã°ÚÁö¸¸, Oracle¿¡¼´Â ¼Ò¼öÁ¡ ´ÜÀ§·Î ÀÏÀÚ°è»êÀÌ °¡´ÉÇÕ´Ï´Ù. MSSQL¿¡¼´Â ÀÏÀÚ¿¡ ¼Ò¼öÁ¡À» »ç¿ëÇصµ ÀνÄÀÌ ºÒ°¡´ÉÇÕ´Ï´Ù.
22. ÀÏÀÚ Â÷ÀÌ °è»ê
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
DATEDIFF
|
+ / -
|
¿ë·Ê
|
SELECTDATEDIFF(dd,'2010/10/07',GETDATE())
|
SELECT SYSDATE - TO_DATE('2010/10/07') FROM DUAL;
|
°á°ú
|
31
|
31.39103009259259259259259259259259259259
|
è À§ °á°ú¿¡µµ ³ªÅ¸³ªµíÀÌ, MSSQL¿¡¼´Â ÀÏÀÚ·Î ÁöÁ¤ÇÏ¸é µü ¶³¾îÁöÁö¸¸, Oracle¿¡¼´Â Ưº°È÷ ÁöÁ¤ÇÏÁö ¾Ê´Â ÇÑ ¼Ò¼öÁ¡±îÁö ³ªÅ¸³À´Ï´Ù.
23. ÇØ´ç ¿ùÀÇ ¸¶Áö¸· ³¯ °¡Á®¿À±â
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
Áö¿øÇÏÁö ¾ÊÀ½
|
LAST_DAY
|
¿ë·Ê
|
-
|
SELECT LAST_DAY(to_date('2010/11/15', 'yyyy/mm/dd')) FROM DUAL;
|
°á°ú
|
-
|
2010/11/30 00:00:00
|
è MSSQL¿¡¼´Â ¾Æ·¡¿Í °°ÀÌ Ã³¸®ÇÒ ¼ö ÀÖ½À´Ï´Ù.
SELECT DATEADD(d,-1,CONVERT(DATETIME,CONVERT(CHAR(6),DATEADD(m,1,'2010-11-15'),112) + '01'))
-- ¼³¸í : ÇØ´çÀÏ¿¡ 1°³¿ùÀ» ´õÇÑ µÚ, ±× ´ÞÀÇ 1ÀÏ¿¡¼ ÇϷ縦 »« ³¯À» °¡Á®¿É´Ï´Ù.
-- °á°ú
2010-11-30 00:00:00.000
|
24. Time Zone¿¡ ÀÇÇÑ ½Ã°£ º¯È¯
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
Áö¿øÇÏÁö ¾ÊÀ½
|
NEW_TIME
|
¿ë·Ê
|
-
|
SELECT NEW_TIME (TO_DATE ('2010/11/07 13:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') FROM DUAL;
|
°á°ú
|
-
|
2010/11/07 10:45:00
|
è AST : ´ë¼¾ç Ç¥ÁؽÃ(ij³ª´Ù µ¿ºÎ, Ǫ¿¡¸£Å丮°í, ¹öÁø¾ÆÀÏ·£µå µî, ±×¸®´ÏÄ¡ Ç¥Áؽú¸´Ù 4½Ã°£ ´ÊÀ½)
MST : »ê¾Ç Ç¥ÁؽÃ(·ÎÅ° »ê¸Æ¿¡ °¡±î¿î ¹Ì±¹°ú ij³ª´Ù ÀϺΠÁö¿ªÀÇ µ¿°è Ç¥ÁؽÃ, ±×¸®´ÏÄ¡ Ç¥Áؽú¸´Ù 7½Ã°£ ´ÊÀ½)
è Oracle Zone Ç¥
Value
|
Description
|
AST
|
Atlantic Standard Time
|
ADT
|
Atlantic Daylight Time
|
BST
|
Bering Standard Time
|
BDT
|
Bering Daylight Time
|
CST
|
Central Standard Time
|
CDT
|
Central Daylight Time
|
EST
|
Eastern Standard Time
|
EDT
|
Eastern Daylight Time
|
GMT
|
Greenwich Mean Time
|
HST
|
Alaska-Hawaii Standard Time
|
HDT
|
Alaska-Hawaii Daylight Time
|
MST
|
Mountain Standard Time
|
MDT
|
Mountain Daylight Time
|
NST
|
Newfoundland Standard Time
|
PST
|
Pacific Standard Time
|
PDT
|
Pacific Daylight Time
|
YST
|
Yukon Standard Time
|
YDT
|
Yukon Daylight Time
|
|
è MSSQL¿¡ DATETIMEOFFSETÀ̶ó´Â °ÍÀÌ ÀÖÁö¸¸, ÀÌ°ÍÀº Ç¥Áؽð£´ë ÀνÄÀÏ »Ó ±¸Á¶´Â ´Ù¸¨´Ï´Ù.
25. ÇØ´çÀÏÀÚ ´ÙÀ½¿¡ ¿À´Â ÇØ´ç ¿äÀÏ ¹Ýȯ
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
Áö¿øÇÏÁö ¾ÊÀ½
|
NEXT_DAY
|
¿ë·Ê
|
-
|
SELECT NEXT_DAY('2010/11/07', '¿ù¿äÀÏ') FROM DUAL;
|
°á°ú
|
-
|
2010/11/08 00:00:00
|
è º¸½Ã´Ù½ÃÇÇ 2010³â 11¿ù 7ÀÏ ÀÌÈÄ¿¡ óÀ½ ¿À´Â ¿ù¿äÀÏÀ» ¹ÝȯÇÕ´Ï´Ù.
ÇÏÁö¸¸, ÁÖÀÇÇÒ »çÇ×Àº ±¹°¡¼³Á¤¿¡ µû¶ó ÀÏÀÚ¼³Á¤ÀÌ ´Ù¸¨´Ï´Ù.
¹Ì±¹À¸·Î µÇ¾î ÀÖÀ¸¸é SELECT NEXT_DAY('10-Nov-07', 'MONDAY') FROM DUAL; À¸·Î ÇÏ¼Å¾ß ÇÕ´Ï´Ù.
26. ÁöÁ¤ÇÑ ³¯Â¥ÀÇ Æ¯Á¤ ºÎºÐÀ» ³ªÅ¸³»´Â ¹®ÀÚ¿À» ¹ÝȯÇÕ´Ï´Ù.
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
DATENAME, DATEPART …
|
TO_CHAR
|
¿ë·Ê
|
SELECT DATENAME(day, '2010-11-07')
SELECT DATENAME(month, '2010-11-07')
SELECT DATENAME(year, '2010-11-07')
SELECT DATEPART(day,'2010-11-07')
SELECT DAY('2010-11-07')
|
SELECT TO_CHAR(TO_DATE('2010/11/07'), 'DD') FROM DUAL;
SELECT TO_CHAR(TO_DATE('2010/11/07'), 'MM') FROM DUAL;
SELECT TO_CHAR(TO_DATE('2010/11/07'), 'YYYY') FROM DUAL;
|
°á°ú
|
7
11
2010
7
7
|
07
11
2010
|
è MSSQL DATENAMEÀÇ °æ¿ì 2005 ÀÌÀü ¹öÀü¿¡ ¸ÂÃçÁ® ÀÖ½À´Ï´Ù. ±× ÀÌ»óÀÇ ¹öÀü¿¡¼´Â ¿ë·Ê ¾Æ·§ºÎºÐÀ» ÂüÁ¶ÇÏ½Ã¸é µË´Ï´Ù.
OracleÀÇ °æ¿ì¿£ TO_CHAR¸¦ ¸¸´ÉÀ¸·Î ½á¼ ÇÒ ¼ö ÀÖ½À´Ï´Ù.
´Ù¸¸ ½ÇÁ¦·Î ¼ýÀÚó·³ ¾²½Ã·Á¸é TO_NUMBER¸¦ ÅëÇؼ ¼ýÀÚÇüÀ¸·Î º¯È¯ÇØ ÁÖ¼Å¾ß ÇÕ´Ï´Ù.
27. ¹®ÀÚÇüÀ» ³¯Â¥ÇüÀ¸·Î º¯È¯
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
CONVERT / CAST
|
TO_DATE
|
¿ë·Ê
|
SELECT CONVERT(DATETIME,'2010-11-07')
SELECT CONVERT(DATETIME,'2010-11-07 12:20:23')
SELECT CONVERT(DATETIME,'20101107')
SELECT CONVERT(DATETIME,'20101107 12:20:23')
SELECT CAST('20101107 12:20:23' ASDATETIME)
|
SELECT TO_DATE('2010-11-07') FROM DUAL;
SELECT TO_DATE('2010/11/07 12:20:23', 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
SELECT TO_DATE('20101107','YYYYMMDD') FROM DUAL;
SELECT TO_DATE('11/07/2010','MM/DD/YYYY') FROM DUAL;
|
°á°ú
|
2010-11-07 00:00:00.000
2010-11-07 12:20:23.000
2010-11-07 00:00:00.000
2010-11-07 12:20:23.000
2010-11-07 12:20:23.000
|
2010/11/07 00:00:00
2010/11/07 12:20:23
2010/11/07 00:00:00
2010/11/07 00:00:00
|
è OracleÀÇ °æ¿ì¿£ Format_Mask¸¦ ÁöÁ¤ÇØ Áֽô °ÍÀÌ °ü·ÊÀÔ´Ï´Ù.
28. ³¯Â¥ÇüÀ» ¹®ÀÚÇüÀ¸·Î º¯È¯
±¸ºÐ
|
MSSQL
|
Oracle
|
ÇÔ¼ö
|
CONVERT / CAST
|
TO_CHAR
|
¿ë·Ê
|
SELECT CONVERT(CHAR(8),GETDATE(),112)
SELECT CONVERT(CHAR(10),GETDATE(),120)
SELECT CONVERT(CHAR(20),GETDATE(),120)
SELECT CONVERT(CHAR(20),GETDATE())
SELECT CAST(GETDATE() AS CHAR(20))
|
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL;
|
°á°ú
|
20101107
2010-11-07
2010-11-07 17:41:21
11 7 2010 5:42PM
11 7 2010 5:41PM
|
2010-11-07 17:43:53
20101107
11/07/2010
|
è ¹®ÀÚÇü°ú ³¯Â¥Çü º¯È¯Àº À§Ã³·³ »ç¿ëÀÌ °¡´ÉÇѵ¥¿ä,
MSSQL¿¡¼ÀÇ ÀÚ¼¼ÇÑ »çÇ×Àº °»ê¾Æ´ÔÀÇ ¾ÆƼŬ
(http://www.sqler.com/?mid=bColumn&page=4&document_srl=265068)
À» ÂüÁ¶ÇØ ÁÖ¼¼¿ä.
Ãâó : http://www.sqler.com/316811