DHJJ [Hatsune's Journal Japan] blog

Hatsune's Journal Japan blog

目次

Blog 利用状況

ニュース

最新ツイート

運営サイト

Hatsune's Journal Japan
DHJJ

著作など

資格など

MVP
Microsoft MVP for Visual Basic
(April 2008 - March 2015)

ACE
OCP
MCP

書庫

日記カテゴリ

わんくま同盟

【Oracle】日付関連のSQL

 Database 10g Enterprise Edition Release 10.2.0.3.0 - Production 
With the Partitioning, OLAP and Data Mining options 
に接続されました。 
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS'; 

セッションが変更されました。 

SQL> 
SQL> SELECT '1年後',SYSDATE,ADD_MONTHS(TRUNC(SYSDATE),12) FROM DUAL; 

'1年 SYSDATE ADD_MONTHS(TRUNC(SY 
------ ------------------- ------------------- 
1年後 2009/01/15 13:48:05 2010/01/15 00:00:00 

SQL> SELECT '1ヶ月後',SYSDATE,ADD_MONTHS(TRUNC(SYSDATE), 1) FROM DUAL; 

'1ヶ月 SYSDATE ADD_MONTHS(TRUNC(SY 
-------- ------------------- ------------------- 
1ヶ月後 2009/01/15 13:48:05 2009/02/15 00:00:00 

SQL> SELECT '1日後',SYSDATE,TRUNC(SYSDATE)+1 FROM DUAL; 

'1日 SYSDATE TRUNC(SYSDATE)+1 
------ ------------------- ------------------- 
1日後 2009/01/15 13:48:05 2009/01/16 00:00:00 

SQL> SELECT '1時間後',SYSDATE,SYSDATE+1/24 FROM DUAL; 

'1時間 SYSDATE SYSDATE+1/24 
-------- ------------------- ------------------- 
1時間後 2009/01/15 13:48:05 2009/01/15 14:48:05 

SQL> SELECT '1分後',SYSDATE,SYSDATE+1/1440 FROM DUAL; 

'1分 SYSDATE SYSDATE+1/1440 
------ ------------------- ------------------- 
1分後 2009/01/15 13:48:05 2009/01/15 13:49:05 

SQL> SELECT '1秒後',SYSDATE,SYSDATE+1/86400 FROM DUAL; 

'1秒 SYSDATE SYSDATE+1/86400 
------ ------------------- ------------------- 
1秒後 2009/01/15 13:48:05 2009/01/15 13:48:06 

SQL> SELECT '明日9時',SYSDATE,TRUNC(SYSDATE)+1+9/24 FROM DUAL; 

'明日9 SYSDATE TRUNC(SYSDATE)+1+9/ 
-------- ------------------- ------------------- 
明日9時 2009/01/15 13:48:05 2009/01/16 09:00:00 

SQL> SELECT '来月1日',SYSDATE,LAST_DAY(TRUNC(SYSDATE))+1 FROM DUAL; 

'来月1 SYSDATE LAST_DAY(TRUNC(SYSD 
-------- ------------------- ------------------- 
来月1日 2009/01/15 13:48:05 2009/02/01 00:00:00 

SQL> SELECT '今月1日',SYSDATE,TRUNC(SYSDATE,'MM') FROM DUAL; 

'今月1 SYSDATE TRUNC(SYSDATE,'MM') 
-------- ------------------- ------------------- 
今月1日 2009/01/15 13:48:05 2009/01/01 00:00:00 

SQL>

1年後 ADD_MONTHS(TRUNC(SYSDATE),12)
1ヵ月後 ADD_MONTHS(TRUNC(SYSDATE), 1)
1時間後 SYSDATE+1/24
1分後 SYSDATE+1/1440
1秒後 SYSDATE+1/86400
明日9時 TRUNC(SYSDATE)+1+9/24
来月1日 LAST_DAY(TRUNC(SYSDATE))+1
今月1日 TRUNC(SYSDATE,'MM')

投稿日時 : 2009年1月18日 19:50

Feedback

# re: 【Oracle】日付関連のSQL 2009/01/19 14:24 こあら

「来月1日」を今月1日にADD_MONTHSを噛ませた書き方にすると、
先月とか翌々月でも字面が揃ってイイかも。
> TRUNC(ADD_MONTHS(SYSDATE, 1),'MM')

# KeIcKshEYK 2011/10/22 22:27 http://www.discountwatchstore.com/Casio-G-Shock_c_

Of course, I understand a little about this post but will try cope with it!!...

# nLvAlfhcbwf 2011/11/02 6:23 http://optclinic.com/

Yeah, it is clear now !... From the very beginning I did not understand where was the connection with the title !!...

# QljDmhviMLNlbLZegep 2011/11/02 9:41 http://travelimagehosting.com/

Are you interested in webmaster`s income?!...

# cPCDVizuarunXpOG 2011/11/09 19:32 http://www.buylasix.biz

Author, Shoot yourself a knee..!

# qOCeysCXpT 2011/11/16 2:59 http://circalighting.com/designer_products.aspx?di

Hello! Read the pages not for the first day. Yes, the connection speed is not good. How can I subscribe? I would like to read you in the future!...

# eSTekPJDtEkV 2011/11/16 4:00 http://www.laurenslinens.com/thomasbedding.html

Received the letter. I agree to exchange the articles.

# nike schuhe selber gestalten 2012/11/17 16:21 http://www.nikeschuheherrende.com/

A toughest way to miss out on someone you care about will be session best adjacent to these businesses bearing in mind you possibly can‘longer you can keep them.
nike schuhe selber gestalten http://www.nikeschuheherrende.com/

# nike free 2012/11/18 23:36 http://www.nikefreerunherrenfrauen.com/

Assume‘MT spend your labour over guy/woman,what person isn‘MT prepared to spend his moment you.
nike free http://www.nikefreerunherrenfrauen.com/

# Pull Gucci 2012/11/25 18:39 http://www.fr-marque.com/

Rarely ever lower, if may unfortunate, do it yourself can never predict who is responsible for cascading fond of your actual laugh.
Pull Gucci http://www.fr-marque.com/

# casquette superman 2013/03/14 0:51 http://www.b77.fr/

Will be last the moment each single buddy thinks brand-new areas such as hook brilliance all over the various. casquette superman http://www.b77.fr/

# casquette obey 2013/03/14 5:08 http://www.b44.fr/

Friends for which you choose equipped with items rrs going to be bought from everyone. casquette obey http://www.b44.fr/

# pari street 2013/03/15 18:16 http://www.a88.fr/

Accord is going to be Coptis groenlandica that scarves the entire spirits with all the self-proclaimed community. pari street http://www.a88.fr/

# chaussea 2013/04/07 2:11 http://ruemee.com/

Real love is simply imperfect through arrival, but it really really evolves more robust as we age if at all as it should be raised on. chaussea http://ruemee.com/

タイトル  
名前  
Url
コメント