SQL 髢「謨ーシ壽律莉倬未謨ー

蜉貂

蟷エ縺ョ蜉邂
ADD_MONTHS(譌・莉伜梛蛻怜錐 , 1 * 12)
ADD_MONTHS( TO_DATE(蛻怜錐,'YYYY/MM/DD'),1 * 12)

譛医ョ蜉邂
ADD_MONTHS(譌・莉伜梛蛻怜錐,1)
ADD_MONTHS( TO_DATE(蛻怜錐,'YYYY/MM/DD') ,1)

譌・縺ョ蜉貂帷ョ
譌・莉伜梛蛻怜錐 + 1
譌・莉伜梛蛻怜錐 – 1
TO_DATE('2005/03/29','YYYY/MM/DD') + 1
TO_DATE('2005/03/29','YYYY/MM/DD') – 1

譎る俣縺ョ蜉貂帷ョ
譌・莉伜梛蛻怜錐 + 1/24
譌・莉伜梛蛻怜錐 – 1/24
TO_DATE('2005/03/29','YYYY/MM/DD') + 1/24
TO_DATE('2005/03/29','YYYY/MM/DD') – 1/24

蛻縺ョ蜉貂帷ョ
譌・莉伜梛蛻怜錐 + 1/24/60
譌・莉伜梛蛻怜錐 – 1/24/60
TO_DATE('2005/03/29','YYYY/MM/DD') + 1/24/60
TO_DATE('2005/03/29','YYYY/MM/DD') – 1/24/60

遘偵ョ蜉貂帷ョ
譌・莉伜梛蛻怜錐 + 1/24/60/60
譌・莉伜梛蛻怜錐 – 1/24/60/60
TO_DATE('2005/03/29','YYYY/MM/DD') + 1/24/60/60
TO_DATE('2005/03/29','YYYY/MM/DD') – 1/24/60/60

譌・莉伜梛螟画焚 + 1
to_date('2018/01/01', 'YYYY/MM/DD') + 1
1譌・蠕

譌・莉伜梛螟画焚 + cast('1 days' AS INTERVAL)
1譌・蠕
譌・莉伜梛螟画焚 + cast('1 months' AS INTERVAL)
1繧ォ譛亥セ
譌・莉伜梛螟画焚 + cast('1 years' AS INTERVAL)
1蟷エ蠕

DECLARE @dteYMD DATETIME

'シ灘ケエ貂帷ョ
SET @dteYMD= DATEADD(yy, -3, @dteNIPYMD)

蜿門セ

譛域忰
LAST_DAY(譌・莉伜梛蛻怜錐)
LAST_DAY(TO_DATE('2005/03/29','YYYY/MM/DD'))

谺。縺ョ謖螳壽屆譌・縺ョ譌・
NEXT_DAY(譌・莉伜梛蛻怜錐, 譖懈律)
NEXT_DAY('2005/03/29' ,1)
NEXT_DAY('2005/03/29' ,'MONDAY')

蟷エ/譛/譌・/譎/蛻/遘
EXTRACT(FORMAT FROM 譌・莉)

EXTRACT(YEAR FROM 譌・莉)
EXTRACT(MONTH FROM 譌・莉)
EXTRACT(DAY FROM 譌・莉)
EXTRACT(HOUR FROM 譌・莉)
EXTRACT(MINUTE FROM 譌・莉)
EXTRACT(SECOND FROM 譌・莉)

蟷エ縺ョ蜿門セ
DECLARE @dteYMD DATETIME
DECLARE @dteYEAR DATETIME

SET @dteYEAR= YEAR(@dteYMD)

譛医ョ蜿門セ
DECLARE @dteYMD DATETIME
DECLARE @dteMONTH DATETIME

SET @dteMONTH= MONTH(@dteYMD)

譌・縺ョ蜿門セ
DECLARE @dteYMD DATETIME
DECLARE @dteDAY DATETIME

SET @dteDAY= DAY(@dteYMD)

DATEPART髢「謨ー
DATEPART( yyyy, TestDate )
竍 2000
DATEPART( d, TestDate )
竍 1
DATEPART( m, TestDate )
竍 4
DATEPART( w, TestDate )
竍 1ス7(譌・縲∵怦窶ヲ驥代∝悄繧定。ィ縺呎焚蟄)

譎る俣蟾ョ

譎る俣蟾ョ(譌・)
'2000-08-25' '2000-08-24'
竊1
'2000-08-25 12:00:00' '2000-08-25 10:00:00'
竊0.083333(2譎る俣蟾ョシ2/24)
'2000-08-25' + '2000-08-24'
繧ィ繝ゥ繝シ

譎る俣蟾ョ(譛)
MONTHS_BETWEEN(譌・莉伜梛蛻怜錐, 譌・莉伜梛蛻怜錐)

MONTHS_BETWEEN('2000-08-25', '2015-06-25')
竊2
MONTHS_BETWEEN('2000-08-25', '2015-05-10')
竊3.48387097
窶サ1繝カ譛域悴貅縺ョ蟾ョ縺後≠繧句エ蜷医ッ31譌・縺ョ譌・蜑イ繧願ィ育ョ励&繧後k縲
3繝カ譛15譌・シ3+15/31=0.48387097

MONTHS_BETWEEN('2000-04-31', '2015-02-28')
竊2
窶サ蠑墓焚縺御ク。譁ケ譛域忰縺ョ蝣エ蜷医ッ譛域焚縺ョ縺ソ縺ョ蟾ョ縺檎ョ怜コ縺輔l繧

譎る俣蟾ョ(蟷エ)
TRUNC(MONTHS_BETWEEN(譌・莉伜梛蛻怜錐, 譌・莉伜梛蛻怜錐) /12)
窶サTRUNCシ壼繧頑昏縺ヲ

蟷エ鮨「
TRUNC(MONTHS_BETWEEN(SYSDATE, 隱慕函譌・) /12)
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('2005/03/29','YYYY/MM/DD')) /12)
窶サSYSDATEシ壹す繧ケ繝繝譌・莉

蛻繧頑昏縺ヲ

date_trunc( 'second', now() )
遘呈悴貅繧貞繧頑昏縺ヲ

Follow me!

谺。縺ョ險倅コ

SQL 髢「謨ーシ壽焚蛟、髢「謨ー