SQL 髢「謨ーシ壼、画鋤髢「謨ー
蝙句、画鋤
TO_NUMBER( 蛟、 )
TO_DATE( 蛟、, 譖ク蠑 )
CAST('123' AS SIGNED) 竊 123
CAST('20200101' AS DATE) 竊 2020-01-01
CAST('2020-01-01' AS DATE) 竊 2020-01-01
CAST('2020/01/01' AS DATE) 竊 2020-01-01
CAST('20200101' AS DATETIME) 竊 2020-01-01 0:00:00
TO_DATE( 蛟、, 譖ク蠑 )
TO_TIMESTAMP( 蛟、, 譖ク蠑 )
( 蛟、 )::TEXT
( 蛟、 )::INT
( 蛟、 )::JSONB
CAST( 蛟、 AS INT)
CAST( 蛟、 AS VARCHAR)
CAST( 蛟、 AS DATE)
CONVERT(CHAR , 蛟、)
CONVERT(VARCHAR , 蛟、)
CONVERT(DATE, 蛟、)
譁蟄怜礼ウサ
繧ォ繝ウ繝槫玄蛻繧(騾夊イィ)
0シ0蝓九a縺吶k/9シ0蝓九a縺励↑縺
SELECT TO_CHAR('1000', 'L9,999,999') FROM MYTABLE;
— ツ・1,000
SELECT TO_CHAR('1000', '$9,999,999') FROM MYTABLE;
— $1,000
SELECT TO_CHAR('1000', '9,999,999') FROM MYTABLE;
— 1,000
SELECT TO_CHAR('1000', 'L0,000,000') FROM MYTABLE;
— ツ・0,001,000
SELECT TO_CHAR('1000', '$0,000,000') FROM MYTABLE;
— $0,001,000
SELECT TO_CHAR('1000', '0,000,000') FROM MYTABLE;
— 0,001,000
SELECT TO_CHAR('1000', '0G000G000') FROM MYTABLE;
— 0,001,000
10000竊10,000
'騾夊イィ蝙九ョ蝣エ蜷
DECLARE @TestValue MONEY
SET @TestValue = 10000
CONVERT(VARCHAR, @TestValue, 1 )
竍堤オ先棡シ10,000
CONVERT(VARCHAR, '10000', 1)
竍堤オ先棡シ10000 '譁蟄怜怜梛縺ョ逶エ謗・螟画鋤縺ッ荳榊庄
'譁蟄怜怜梛縺ョ蝣エ蜷医ッ騾夊イィ蝙九↓螟画鋤縺励※縺九i
CONVERT(VARCHAR, CONVERT(MONEY, '10000'), 1)
竍堤オ先棡シ10,000
蟆乗焚轤ケ陦ィ遉コ
竊 0,000.250 窶サ謨エ謨ー驛ィシ0蝓九a 蟆第焚驛ィシ0蝓九a
SELECT TO_CHAR(0.25, '9,999.999') FROM MYTABLE;
竊偵縲.250 窶サ謨エ謨ー驛ィシ壹せ繝壹シ繧ケ蝓九a 蟆第焚驛ィシ0蝓九a
SELECT TO_CHAR(10.25, '0,000.000') FROM MYTABLE;
竊 0,010.250 窶サ謨エ謨ー驛ィシ0蝓九a 蟆第焚驛ィシ0蝓九a
SELECT TO_CHAR(10.25, '9,999.999') FROM MYTABLE;
竊偵縲10.250 窶サ謨エ謨ー驛ィシ壹せ繝壹シ繧ケ蝓九a 蟆第焚驛ィシ0蝓九a
SELECT TO_CHAR(10.25, '9G999D999') FROM MYTABLE;
竊偵縲10.250
濶イ縲縺ェ譌・莉倩。ィ遉コ
UPDATE MYTABLE SET DATE1 = '2015-06-18';
SELECT * FROM MYTABLE;
SELECT TO_CHAR(DATE1,'RR-MM-DD') FROM MYTABLE;
竊'15-06-18'
SELECT TO_CHAR(DATE1,'YY-MM-DD') FROM MYTABLE;
竊'15-06-18'
SELECT TO_CHAR(DATE1,'YY-MON-DD') FROM MYTABLE;
竊'15-6譛 -18'
SELECT TO_CHAR(DATE1,'RR-MM-DD(D)') FROM MYTABLE;
竊'15-06-18(7)'
譌・シ1 譛茨シ2 轣ォシ3 豌エシ4 譛ィシ5 驥托シ6 蝨滂シ7
SELECT ~ ORDER BY TO_CHAR(DATE1,'D')
譖懈律鬆(譌・譖懷磯ュ)
SELECT ~ ORDER BY TO_CHAR(DATE1 – 1,'D')
譖懈律鬆(譛譖懷磯ュ)
SELECT TO_CHAR(DATE1,'RR-MM-DD(DD)') FROM MYTABLE;
竊'15-06-18(蝨)'
SELECT TO_CHAR(DATE1,'RR-MM-DD(DAY)') FROM MYTABLE;
竊'15-06-18(蝨滓屆譌・)'
SELECT TO_CHAR(DATE1,'YY-MON-DD', 'NLS_DATE_LANGUAGE=ENGLISH') FROM MYTABLE;
竊'15-JUN-18'
SELECT TO_CHAR(DATE1,'YY-MON-DD', 'NLS_DATE_LANGUAGE=JAPANESE') FROM MYTABLE;
竊'15-6譛 -18'
SELECT TO_CHAR(DATE1,'RR-MM-DD HH24:MI:SS') FROM MYTABLE;
竊'15-06-18 00:00:00'
SELECT TO_CHAR(DATE1,'YY-MM-DD HH24:MI:SS') FROM MYTABLE;
竊'15-06-18 00:00:00'
SELECT TO_CHAR(DATE1,'RR-MM-DD HH12:MI:SS') FROM MYTABLE;
竊'15-06-18 12:00:00'
SELECT TO_CHAR(DATE1,'YY-MM-DD HH12:MI:SS') FROM MYTABLE;
竊'15-06-18 12:00:00'
SELECT TO_CHAR(DATE1,'RR-MM-DD HH:MI:SS') FROM MYTABLE;
竊'15-06-18 12:00:00'
SELECT TO_CHAR(DATE1,'YY-MM-DD HH:MI:SS') FROM MYTABLE;
竊'15-06-18 12:00:00'
SELECT TO_CHAR(DATE1,'RRRR-MM-DD HH24:MI:SS') FROM MYTABLE;
竊'2015-06-18 00:00:00'
SELECT TO_CHAR(DATE1,'YYYY-MM-DD HH24:MI:SS') FROM MYTABLE;
竊'2015-06-18 00:00:00'
SELECT TO_CHAR(DATE1,'AM HH24:MI:SS') FROM MYTABLE;
SELECT TO_CHAR(DATE1,'PM HH24:MI:SS') FROM MYTABLE;
竊'蜊亥燕 00:00:00'
窶サAM/PM繧呈欠螳壹☆繧九→蟶ク縺ォ豁」縺励>蜊亥燕/蜊亥セ後′陦ィ遉コ縺輔l繧'
SELECT TO_CHAR(DATE1,'YY-MON-DD HH24:MI:SS', 'NLS_DATE_LANGUAGE=JAPANESE') FROM MYTABLE;
竊'15-6譛 -18 09:09:09'
SELECT TO_CHAR(DATE1,'fmYY-MON-DD HH24:MI:SS', 'NLS_DATE_LANGUAGE=JAPANESE') FROM MYTABLE;
竊'15-6譛-18 9:9:9' 窶サ繧ケ繝壹シ繧ケ縲0蜑企勁
SELECT TO_CHAR(DATE1,'fmYY-MON-DD HH24:fmMI:SS',
'NLS_DATE_LANGUAGE=JAPANESE') FROM MYTABLE;
竊'15-6譛-18 9:09:09' 窶サ隍謨ー蝗槭ョfm縺ァ譖エ縺ォ蛻譖ソ
SELECT TO_CHAR(SYSDATE,'YY-MM-DDTH') FROM MYTABLE;
竊'15-06-08TH' 窶サ譌・莉倥ョ譁蟄苓。ィ險
SELECT TO_CHAR(SYSDATE,'YY-MM-DDSP') FROM MYTABLE;
竊'15-06-EIGHTEEN' 窶サ譌・莉倥ョ譁蟄苓。ィ險
SELECT TO_CHAR(SYSDATE,'YY-MM-DDSPTH') FROM MYTABLE;
竊'15-06-EIGHTEENTH' 窶サ譌・莉倥ョ譁蟄苓。ィ險
繝溘Μ遘偵ョ螟画鋤
SELECT TO_CHAR(SYSTIMESTAMP,'YY-MM-DD HH24:MI:SS.FF3') FROM MYTABLE;
WHERE蜿・縺ァ譌・莉伜梛繧呈欠螳壹☆繧区凾縺ョ豕ィ諢冗せ
DATE1シ'15-06-18 10:30:00'縺ョ蝣エ蜷医
SELECT * FROM MYTABLE WHERE DATE1 = '15-06-18'
縺ァ縺ッ謚ス蜃コ縺ァ縺阪↑縺縲
SELECT * FROM MYTABLE WHERE TRUNC(DATE1) = '15-06-18'
窶サ譎る俣驛ィ蛻縺ョ蛻繧頑昏縺ヲ(='15-06-18 00:00:00')
SELECT * FROM MYTABLE WHERE TO_CHAR(DATE1,'YY-MM-DD') = '15-06-18'
窶サ'YY-MM-DD'譖ク蠑上〒譁蟄怜怜喧(='15-06-18')
to_char(now(), ‘YYYY/MM/DD HH24:MI:SS’)
('2018-01/01')::TEXT
CAST('2018-01/01' AS VARCHAR)
莉
竊偵縲9,99 9.990
謨ー蛟、邉サ
謨ー蛟、蛻、螳
DECLARE @TestValue VARCHAR(2)
SET @TestValue = 'A'
CASE ISNUMERIC( @TestValue )
WHEN 0 THEN '0'
WHEN 1 THEN @TestValue
END AS STAN
窶サ謨ー蛟、莉・螟悶ョ譎ゅッ0縺ォ螟画鋤
譌・莉倡ウサ
SELECT TO_DATE('2015/01/01 10:10:10', 'YYYY/MM/DD HH:MI:SS') FROM MYTABLE;
SELECT TO_DATE('15/01/01 10:10:10', 'YY/MM/DD HH:MI:SS') FROM MYTABLE;
SELECT TO_DATE('150101 101010', 'YYMMDD HHMISS') FROM MYTABLE;
縲仙、画鋤譎ゑシ壽嶌蠑上r逵∫払縺励◆蝣エ蜷医
SELECT TO_DATE("02:00:00","HH:MI:SS") FROM MYTABLE;
SELECT TO_CHAR(TO_DATE("02:00:00","HH:MI:SS"),"YYYY/MM/DD HH24:MI:SS") FROM MYTABLE;
– – 2015/07/01 02:00:00 窶サ蟷エ譛域律繧堤怐逡・縺励◆蝣エ蜷医∝ケエ譛医ッ繧キ繧ケ繝繝譌・莉倥′驕ゥ逕ィ縺輔l繧九よ律縺ッ蠑キ蛻カ01譌・縲
SELECT TO_DATE("000101","RRMMDD") FROM MYTABLE;
SELECT TO_CHAR(TO_DATE("000101","RRMMDD"),"YYYY/MM/DD HH24:MI:SS") FROM MYTABLE;
– – 2000/01/01 00:00:00 譎ょ綾繧堤怐逡・縺励◆蝣エ蜷医00:00:00縺碁←逕ィ縺輔l繧九
縲慎IMESTAMP蝙九
TO_TIMESTAMP('2016-04-01 00:00:00' , 'YYYY-MM-DD HH24:MI:SS')
to_timestamp( '2018-01-01 10:00:00' , 'YYYY-MM-DD HH24:MI:SS' )
CAST('2018-01-01' AS DATE)
SELECT date_format(蛻怜錐, '%H:%i:%s') as time FROM 陦ィ蜷;
SET @dteFuriDay = CONVERT(DATE,'20130101')
@dteFuriDayシ2013-01-01
DECLARE @chrFuriDay VARCHAR(8);
SET @chrFuriDay = CONVERT(VARCHAR,@dteFuriDay,111)
@chrFuriDayシ2013/01/01
SET @chrFuriDay = CONVERT(VARCHAR,@dteFuriDay,112)
@chrFuriDayシ20130101
NULL
NULL蛟、縺ョ蛻、螳壹サ螟画鋤
竊0
NVL2(ス1, ス2, ス3)
ス1縺君ULL縺ァ縺ェ縺譎や抵ス2繧偵¨ULL縺ョ譎や抵ス3繧定ソ斐☆縲
SELECT NVL2('NULL',1, 0) FROM MYTABLE;
竊1
SELECT NVL2((NULL),1, 0) FROM MYTABLE;
竊0
謌サ繧雁、シ夂ャャ莠悟シ墓焚
隨ャ莠悟シ墓焚縺ョ蝙 != 隨ャ荳牙シ墓焚縺ョ蝙 縺ョ蝣エ蜷 竊 隨ャ莠悟シ墓焚縺ョ蝙九∈證鈴サ吝、画鋤
NULLIF(ス1, ス2)
ス1 = ス2縺ョ譎や誰ULL繧偵ス1 竕 ス2縺ョ譎や抵ス1繧定ソ斐☆
COALESCE(ス1, ス2, ス3)
繝ェ繧ケ繝亥縺九iNULL縺ァ辟。縺蛟、繧定ソ斐☆(繧ウ繧ヲ繧「繝ャ繧ケ)
繝ェ繧ケ繝茨シ哢ULL, NULL, 5縺ョ譎や5
繝ェ繧ケ繝茨シ哢ULL, NULL, NULL縺ョ譎や誰ULL
繝ェ繧ケ繝亥縺ッ蜈ィ縺ヲ蜷悟梛縺ョ蠢隕√≠繧
DECODE髢「謨ー繧堤畑縺縺櫻ULL螟画鋤繧ょ庄閭ス
NULL縺ョ螟画鋤
TO_CHAR( (NULL) )
竊(NULL)
TO_NUMBER( (NULL) )
竊(null)
TO_DATE( (NULL) )
竊(null)
NULL縺ョ髮險医ッ縲SQL 繧ー繝ォ繝シ繝鈴未謨ー/NULL縺ョ髮險縲榊盾辣ァ
coalesce( 蛻怜錐, 0 )
遨コ譁蟄怜、画鋤
coalesce( nullif(蛻怜錐,''), '0' )
Null縺ョ蝣エ蜷医シ舌r蜿門セ
SELECT ISNULL( 蛻怜錐, '' ) FROM ス
Null縺ョ蝣エ蜷医''繧貞叙蠕