|
01. select
02. Date () as 日付部分
03. , Time () as 時間部分
04. ,Now() as 日付と時間1
05. ,Now as 日付と時間2
06. , Year ( '2010/01/21 1:02:03' ) as 年1
07. , Month ( '2010/01/21 1:02:03' ) as 月1
08. , Day ( '2010/01/21 1:02:03' ) as 日1
09. , Hour ( '2010/01/21 1:02:03' ) as 時1
10. , Minute ( '2010/01/21 1:02:03' ) as 分1
11. , Second ( '2010/01/21 1:02:03' ) as 秒1
12. ,Now() + 1 as 日数加算1
13. ,DateAdd( 'd' , 1, Now()) as 日数加算2
14. ,DateAdd( 'm' , -1, Now()) as 月数加算
15. ,DateAdd( 'yyyy' , 1, Now()) as 年数加算
16. ,DateAdd( 'ww' , -1, Now()) as 週数加算
17. ,DateDiff( 'd' , '2005/01/01' , Now()) as 経過日数
18. ,DatePart( 'yyyy' , Now()) as 年2
19. ,DatePart( 'm' , Now()) as 月2
20. ,DatePart( 'd' , Now()) as 日2
21. ,DatePart( 'h' , Now()) as 時2
22. ,DatePart( 'n' , Now()) as 分2
23. ,DatePart( 's' , Now()) as 秒2
24. ,DatePart( 'w' , Now()) as 曜日
25. ,DatePart( 'ww' , Now()) as 週
26. ,DatePart( 'y' , Now()) as 年間通算日
27. ,DateValue( 'H1/01/01' ) as 平成
28. ,DateValue( 'S1/01/01' ) as 昭和
29. ,DateValue( 'T1/01/01' ) as 大正
30. ,DateValue( 'M1/01/01' ) as 明治
31. ,Format( '2005/01/01' , 'yyyy/MM/dd' ) as 日付文字列1
32. ,Format( '2005/01/01' , 'yyyy/M/d' ) as 日付文字列2
日付部分 | 時間部分 | 日付と時間1 | 日付と時間2 | 年1 |
2010/01/21 | 2:12:38 | 2010/01/21 2:12:38 | 2010/01/21 2:12:38 | 2010 |
月1 | 日1 | 時1 | 分1 | 秒1 |
1 | 21 | 1 | 2 | 3 |
日数加算1 | 日数加算2 | 月数加算 | 年数加算 | 週数加算 |
2010/01/22 2:12:38 | 2010/01/22 2:12:38 | 2009/12/21 2:12:38 | 2011/01/21 2:12:38 | 2010/01/14 2:12:38 |
経過日数 | 年2 | 月2 | 日2 | 時2 |
1846 | 2010 | 1 | 21 | 2 |
分2 | 秒2 | 曜日 | 週 | 年間通算日 |
12 | 38 | 5 | 4 | 21 |
平成 | 昭和 | 大正 | 明治 | 日付文字列1 |
1989/01/01 | 1926/01/01 | 1912/01/01 | 1868/01/01 | 2005/01/01 |
日付文字列2 | | | | |
2005/1/1 | | | | |
|
select
switch(
Weekday(now)=1,'日'
,Weekday(now)=2,'月'
,Weekday(now)=3,'火'
,Weekday(now)=4,'水'
,Weekday(now)=5,'木'
,Weekday(now)=6,'金'
,Weekday(now)=7,'土'
) as 曜日1
,switch(
Weekday(now,2)=1,'月'
,Weekday(now,2)=2,'火'
,Weekday(now,2)=3,'水'
,Weekday(now,2)=4,'木'
,Weekday(now,2)=5,'金'
,Weekday(now,2)=6,'土'
,Weekday(now,2)=7,'日'
) as 曜日2;
| |
|
Microsoft ドキュメント
Format 関数の日付と時間の書式
DateValue 関数
Weekday 関数
|
|
|
select
GETDATE() + 1 as 日数加算1
,DATEADD(d, 1, GETDATE()) as 日数加算2
,DATEADD(m, -1, GETDATE()) as 月数加算
,DATEADD(yy, 1, GETDATE()) as 年数加算
,DATEADD(ww, -1, GETDATE()) as 週数加算
,DATEDIFF(d, '2005/01/01', GETDATE()) as 経過日数
,DATEPART(d, GETDATE()) as 日
,DATEPART(y, GETDATE()) as 年間通算日
,DATEPART(hh, GETDATE()) as 時
,DATEPART(n, GETDATE()) as 分
,DATEPART(s, GETDATE()) as 秒
,DATEPART(ms, GETDATE()) as milli秒
,DATEPART(m, GETDATE()) as 月
,DATEPART(w, GETDATE()) as 曜日
,DATEPART(ww, GETDATE()) as 週
,DATEPART(yyyy, GETDATE()) as 年
,CONVERT(varchar, GETDATE(),120) as 日付編集1
,CONVERT(varchar, GETDATE(),111) as 日付編集2
,CONVERT(varchar, GETDATE(),11) as 日付編集3
,CONVERT(varchar, GETDATE(),112) as 日付編集2
,CONVERT(varchar, GETDATE(),12) as 日付編集3
| |
|
Microsoft の CONVERT関数 のスタイル一覧 へのリンク
●日数加算1 | ●日数加算2 | ●月数加算 | ●年数加算 | |
2008/03/26 10:52:30 | 2008/03/26 10:52:30 | 2008/02/25 10:52:30 | 2009/03/25 10:52:30 | |
●週数加算 | ●経過日数 | ●日 | ●年間通算日 | |
2008/03/18 10:52:30 | 1179 | 25 | 85 | |
●時 | ●分 | ●秒 | ●milli秒 | |
10 | 52 | 30 | 173 | |
●月 | ●曜日 | ●週 | ●年 | |
3 | 3 | 13 | 2008 | |
●日付編集1 | ●日付編集2 | ●日付編集3 | ●日付編集2 | ●日付編集3 |
2008-03-25 10:52:30 | 2008/03/25 | 08/03/25 | 20080325 | 080325 |
|
|
|
select
NOW() + INTERVAL 1 DAY as 日数加算1
,DATE_ADD(NOW(),INTERVAL 1 DAY) as 日数加算2
,DATE_ADD(NOW(),INTERVAL -1 MONTH) as 月数加算
,DATE_ADD(CURDATE(),INTERVAL 1 YEAR) as 年数加算
,TO_DAYS(NOW()) - TO_DAYS(CAST('2005/01/01' as DATETIME)) as 経過日数
,DATE_FORMAT(NOW(),'%d') as 日
,DATE_FORMAT(NOW(),'%j') as 年間通算日
,DAYOFYEAR(NOW()) as 年間通算日
,DATE_FORMAT(NOW(),'%H') as 時
,DATE_FORMAT(NOW(),'%i') as 分
,DATE_FORMAT(NOW(),'%S') as 秒
,DATE_FORMAT(NOW(),'%m') as 月
,DATE_FORMAT(NOW(),'%w') as 曜日
,DATE_FORMAT(NOW(),'%U') as 週
,DATE_FORMAT(NOW(),'%Y') as 年
,DATE_FORMAT(NOW(),'%Y-%m-%d') as 日付
| |
|
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
日数加算1 | 日数加算2 | 月数加算 | 年数加算 | 経過日数 | 日 | 年間通算日 | 年間通算日 |
2009-03-14 16:18:34 | 2009-03-14 16:18:34 | 2009-02-13 16:18:34 | 2010-03-13 | 1532 | 13 | 072 | 72 |
時 | 分 | 秒 | 月 | 曜日 | 週 | 年 | 日付 |
16 | 18 | 34 | 03 | 5 | 10 | 2009 | 2009-03-13 |
|
|
環境変数 TZ=JST-9
|
select
now(),localtime
,now() + interval '1 day' as 日数加算
,now() + interval '-1 month' as 日数加算
,now() + interval '1 year' as 年数加算
,current_date - '2005/01/01' as 経過日数
,date_part('day', now()) as 日
,date_part('doy', now()) as 年間通算日
,date_part('hour', now()) as 時
,date_part('minute', now()) as 分
,date_part('second', now()) as 秒
,date_part('milliseconds', now()) as milli秒
,date_part('month', now()) as 月
,date_part('dow', now()) as 曜日
,date_part('week', now()) as 週
,date_part('year', now()) as 年
,to_char(now(), 'YYYY/MM/DD HH24:MI:SS') as 日付文字列1
,to_char(now(), 'YYYY/FMMM/FMDD FMHH24:FMMI:FMSS') as 日付文字列2
| |
|
1 | 2 | 3 | 4 | 5 | 6 |
now | time | 日数加算 | 日数加算 | 年数加算 | 経過日数 |
2009-03-13 16:31:50.503238+09 | 16:31:50.503238 | 2009-03-14 16:31:50.503238+09 | 2009-02-13 16:31:50.503238+09 | 2010-03-13 16:31:50.503238+09 | 1532 |
日 | 年間通算日 | 時 | 分 | 秒 | milli秒 |
13 | 72 | 16 | 31 | 50.503238 | 50503.238 |
月 | 曜日 | 週 | 年 | 日付文字列1 | 日付文字列2 |
3 | 5 | 11 | 2009 | 2009/03/13 16:31:50 | 2009/3/13 16:31:50 |
|
|
|
select
sysdate
,sysdate + 1 as 日数加算
,ADD_MONTHS( sysdate, -1 ) as 月数加算
,ADD_MONTHS( sysdate, 1 * 12 ) as 年数加算
,TRUNC(sysdate)-TO_DATE('2005/01/01') as 経過日数
,EXTRACT(day from sysdate) as 日
,EXTRACT(month from sysdate) as 月
,EXTRACT(year from sysdate) as 年
,TO_CHAR(sysdate,'HH24') as 時
,TO_CHAR(sysdate,'MI') as 分
,TO_CHAR(sysdate,'SS') as 秒
,TO_CHAR(sysdate,'DDD') as 年間通算日
,TO_CHAR(sysdate,'D') as 曜日
,TO_CHAR(sysdate,'WW') as 週
,TO_CHAR(sysdate,'YYYY/MM/DD HH24:MI:SS') as 日付文字列
,MONTHS_BETWEEN(sysdate,'2005/01/01') as 期間月数
,TO_CHAR(LAST_DAY(sysdate),'DD') as 月の最終日
from dual
| |
|
|
|