ITコーディネータのシュウです。

今年は暖冬と言われており、スキー場では雪不足のところも結構あって心配されていますが、急に寒くなったりもしますね。暖冬というのは、暖かい日が続くという意味ではなく、平均して暖かいということなので、寒暖の差が激しい場合も多いようです。寒暖の差が激しいと、自律神経なども乱れがちになり、体調を崩しやすいそうです。
年末も近づいてきましたが、体調の管理には十分注意して、よい年を迎えたいものですね。
また、ブログを見て頂いた皆さん、この1年、どうもありがとうございました。来年も頑張りますので、よろしくお願いいたします！

＜本日の題材＞
DATEFROMPARTS, DATETIME2FROMPARTS関数

前回、日付の曜日を取得する関数として、SQL ServerのDATEPART関数、DATENAME関数というものを取り上げましたが、今回も日付に関連する内容です。

いろいろなシステムにおいて、年や月を指定してその期間内のデータについての処理を行うことがあると思います。そのとき、数値などで指定した年、月から日付型に変換するのにCAST関数などを使用して文字に変換してそれぞれをつなげ、最後にCONVERT関数などで日付に変換して対応するようなことを行う必要が出てくると思いますが、少し便利な関数を見つけましたので、今回はこれを取り上げてみたいと思います。（SQL Serverの場合）

その関数は、DATEFROMPARTS関数、DATETIMEFROMPARTS関数、DATETIME2FROMPARTS関数というものです。SQL Server2012から有効な関数です。

まず、DATEFROMPARTS関数についてですが、構文は、
DATEFROMPARTS (year, month, day)
引数の year, month, day のところに、それぞれ年、月、日を示す整数を設定することで、日付に変換して結果を返します。

例）
年、月、日をそれぞれ変数を持たせて値を設定し、それらの値に該当する日付を日付型で抽出します。

DECLARE
  @年 INT = 2015
, @月 INT = 12
, @日 INT = 21

SELECT DATEFROMPARTS (@年, @月, @日);

簡単に日付型のデータに変換できました。
これは、EXCELのDATE関数と同じようなイメージですね。

※SQL Server 2008で上記の処理を実行すると、下記のようにエラーになります。

 ※また、月の引数部分を13にするとか、日の引数部分を32など日付として存在しない値を設定した場合もエラーになります。

上記を、SQL Server2012より以前の場合など、この関数を使用しないで別のやり方でやろうとすると、例えば以下のようにする方法があります。

DECLARE
 @年 INT = 2015
, @月 INT = 12
, @日 INT = 21
, @年月日 VARCHAR(8) 

SET @年月日 = CAST(@年 AS VARCHAR)+CAST(@月 AS VARCHAR)+CAST(@日 AS VARCHAR)

SELECT CONVERT(date, @年月日)

 ただ、月や日が1桁の場合や、年が4桁に満たないような場合には、以下のようにエラーになってしまいますので、もう一工夫必要になります。

DECLARE
  @年 INT = 2015
, @月 INT = 9
, @日 INT = 1
, @年月日 VARCHAR(8) 

SET @年月日 = CAST(@年 AS VARCHAR)+CAST(@月 AS VARCHAR)+CAST(@日 AS VARCHAR)

SELECT CONVERT(date, @年月日)

これは、上記の @年月日 が「201591」となって日付に変換しようとしてもできないためです。
ですので、以下のように、左側に「0」を詰めるかたちにする必要があります。

SET @年月日 = RIGHT('0000'+CAST(@年 AS VARCHAR), 4) + RIGHT('00'+CAST(@月 AS VARCHAR), 2) + RIGHT('00'+CAST(@日 AS VARCHAR), 2)

実際にやってみると、

上記のようにすると、@年月日は「20150901」となるので、その後のCONVERT関数でエラーにならずに、日付に変換できました。

次に、時刻についても時間や分、秒を同様に引数で渡して datetime2型などで表示したい場合には、DATETIME2FROMPARTS関数というものもあります。
（datetime型にする場合は DATETIMEFROMPARTS関数。datetime型とdatetime2型の違いについては、以前の投稿（Oracle・SQL Server 日付型について)で記載しています）

下記に例を示します。

DECLARE
  @年 INT = 2015
, @月 INT = 12
, @日 INT = 21
, @時 INT = 16
, @分 INT = 42
, @秒 INT = 34
, @秒_小数 INT = 525

SELECT DATETIME2FROMPARTS (@年, @月, @日, @時, @分, @秒, @秒_小数, 3);

指定した値を元に、datetime2型で表示されているのがわかります。
構文は、

DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)

引数については下記：
year     ：年を指定する整数式。
month    ：月を指定する整数式。
day      ：日を指定する整数式。
hour     ：時間を指定する整数式。
minute   ：分を指定する整数式。
seconds  ：秒を指定する整数式。
fractions：小数部分を指定する整数式。
precision：返される datetime2 値の有効桁数を指定する整数リテラル。

さて、ORACLEで同様のことをする場合、下記のように、それぞれの数値を一旦TO_CHAR関数で文字でつなげた後に、日付型（今回はミリ秒があるのでTIMESTAMP型）にTO_TIMESTAMP関数で変換するなどの処理が必要になります。

※今回は、TIMESTAMP型に変換した後、SQL Serverのときと同様の形式で結果を表示したかったので、最後にTO_CHAR関数で書式を整えています。

DECLARE
  V_年 INT := 2015;
  V_月 INT := 9;
  V_日 INT := 1;
  V_時 INT := 16;
  V_分 INT := 42;
  V_秒 INT := 34;
  V_秒_小数 INT := 525;
  V_日付文字 VARCHAR2(30);
  V_日付 TIMESTAMP;
  V_日付変換 VARCHAR2(30);

BEGIN
  V_日付文字 := TRIM(TO_CHAR(V_年,'0999'))||TRIM(TO_CHAR(V_月,'09'))||TRIM(TO_CHAR(V_日,'09'))||TRIM(TO_CHAR(V_時,'09'))||TRIM(TO_CHAR(V_分,'09'))||TRIM(TO_CHAR(V_秒,'09'))||TRIM(TO_CHAR(V_秒_小数,'099'));

  DBMS_OUTPUT.PUT_LINE(V_日付文字); 

  SELECT TO_TIMESTAMP(V_日付文字, 'YYYY-MM-DD HH24:MI:SS FF3') INTO V_日付 FROM DUAL;

  DBMS_OUTPUT.PUT_LINE(V_日付);

  SELECT TO_CHAR(V_日付, 'YYYY-MM-DD HH24:MI:SS.FF3') INTO V_日付変換 FROM DUAL;

  DBMS_OUTPUT.PUT_LINE(V_日付変換);
END;
/

数値で設定した年、月、日、時間、分、秒、ミリ秒の値を元に、V_日付という TIMESTAMP型の変数に変換された値が設定されていることが確認できます。

※月で1桁のものを2桁の文字で表示する場合などに、書式としてTO_CHAR(項目, ‘09’)というようにすると、2桁に足らない場合には、頭に「0」を埋めてくれます。

また、ミリ秒を設定するときの書式は、「FF[1-9]」というものがあり、今回の小数点以下3桁の場合は、「FF3」と設定します。

今日は以上まで

にほんブログ村

 ITコーディネータのシュウです。

 先日、長女がイルミージュという西武遊園地で行われているイルミネーションを見に行って、それを撮った写真をくれたので、アップしてみました。10月24日から来年の4月10日まで16:00～21:00の間、開催しているようですね。いろいろと写真をくれたのですが、どれもとってもきれいでした。この時期は、各地でイルミネーションの催しがありますが、西武遊園地については知らなかったです。

西武遊園地と言えば、妻と一緒になる前に二人で行ったことのある特別な思い出の場所でもあり、そのときのことを懐かしく思い出します。あの頃は、妻に会えることが本当に嬉しくて、二人でアトラクションに乗ったことも結構鮮明に覚えていますね。
まだこういうイルミネーションなどはなかったですけどね。
しばらく忘れていた記憶を甦らせてくれた娘に感謝します。

さて話は変わりますが、dbSheetのユーザ事例として、Access版を採用した産業用電気機器卸売業の会社の事例がホームページに上がっています。既存のAccessの営業支援システムを、データをRDBMSで一元管理しつつ、国内10拠点で運用可能なシステムに短期間で移行させることができたという内容です。興味のある方はぜひご参照ください。
http://www.newcom07.jp/dbsheetclient/usrvoice/electric_wholesale.html

＜本日の題材＞
DATEPART, DATENAME関数

最近関わったシステムで、日付とともに曜日を各列の列名の部分に表示しつつ、各日毎の計画数量などの集計値を各行に表示する帳票を作成するというものがありました。
対象のDBがSQL Serverであったので、対象の日付の曜日を表示するのに、DATEPART関数というものを使用しました。今回はこれを取り上げてみたいと思います。

構文は、
DATEPART(datepart, date)

引数としての datepart には日付の要素を指定し、その後ろに確認したい日付を指定します。この関数の戻り値は int型になります。
日付の要素としての datepart には以下のようなものがあります。

  year, yyyy, yy  ：年
  quarter, qq, q  ：4半期
　month, mm, m ：月
　dayofyear, dy, y ：年の何日目
　day, dd, d      ：日
　week, wk, ww  ：年の第何週
　weekday, dw   ：曜日
　hour, hh       ：時刻の何時
　minute, n      ：時刻の分
　second, ss, s    ：時刻の秒
　millisecond, ms ：時刻のミリ秒
　microsecond, mcs ：時刻のマイクロ秒
　nanosecond, ns ：時刻のナノ秒
　…..他

今回は、曜日を確認したいので、weekday または dw を使います。
ただし、戻り値は、SET DATEFIRST を使って設定された値に依存します。SET DATEFIRST は、週の最初の曜日を示す整数値を指定するもので、SET DATEFIRST 1 というようにしますが、
  1：月曜日
  2：火曜日
  3：水曜日
  4：木曜日
  5：金曜日
  6：土曜日
  7：日曜日（デフォルト）
となります。カレンダーを見ても、1週間の始まりが日曜日というのが既定値ですね。

例）
それでは、今日の日付が何曜日なのかを確認します。
まず、今日が何日かを確認します。

SELECT CONVERT(VARCHAR, GETDATE(), 111)

「2015-12-13」は日曜日ですが、SQLでDATEPART関数を使って確認すると、

SELECT DATEPART(dw, GETDATE())

デフォルトの状態（SET DATEFIRST 7）では、日曜日を週の最初と見なすので、結果は以下のように「1」となります。

ついでに、今年の第何週目かを確認すると、

SELECT DATEPART(wk, GETDATE())

また、DATEPART関数は、戻り値が int型でしたが、戻り値が nvarchar型で同様に日付の要素についての結果を戻す関数として、DATENAME関数というものがあります。

SELECT DATENAME(dw, GETDATE())

「日曜日」ではなく、最初の「日」だけでよければ、最初の1文字のみを取得すればよいと思います。

ときに、システムによっては、月曜日を週の開始日として、曜日や第何週目かを確認したい場合もあるかも知れません。このときは SET DATEFIRST 1 としてから上記の処理を実施します。確認してみると、

SET DATEFIRST 1
SELECT DATEPART(dw,GETDATE()), DATEPART(wk,GETDATE());

SELECT DATENAME(dw, GETDATE()), LEFT(DATENAME(dw, GETDATE()),1);

 週の始まりが月曜日という指定をすることで、今日「2015-12-13」は、DATEPART(dw, GETDATE())では「7」、そして第50週めという結果になりました。DATENAMEでの曜日は日曜日で同じ結果になります。

上記は、ORACLEでも TO_CHAR関数を使用することで同様な内容を行うことができます。
まず、今日の日付を確認します。

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

次に、今日が何曜日か、年初から第何週目か、また月での何週目かを確認します。

SELECT
  TO_CHAR(SYSDATE, 'D')    AS 曜日
, TO_CHAR(SYSDATE, 'DY')   AS 曜日名略
, TO_CHAR(SYSDATE, 'DAY')  AS 曜日名
, TO_CHAR(SYSDATE, 'WW')   AS 年初からの週
, TO_CHAR(SYSDATE, 'W')    AS 月初からの週
  FROM DUAL;

Oracleでは、TO_CHAR関数を利用して様々な内容を取得してくることができますね。

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

春先に京都に行った時の金閣寺の写真です。せっかく撮っていたのでアップします。

金閣寺は正式には鹿苑寺といい、写真の金閣（舎利殿）を含む鹿苑寺が世界文化遺産として登録されています。現在の金閣は、三島由紀夫の小説金閣寺にもあるように、1950年の失火で国宝であった当時の金閣が全焼した後、1955年に再建されたものです。
再建にあたっては焼失直前の姿ではなく創建時の姿を再現するとの方針が採られ、現在のような金色に光る豪華なかたちになったとのこと。
ただ、国宝や重要文化財がこのような人為的な火災や、災害などで損失してしまうことは本当に残念です。文部科学省でもこのような文化財の総合防災対策などリスク管理についてはいろいろと検討・対策を取られているようです。

実は、最近仕事も忙しくなり、少々疲れ気味。久しぶりの投稿です。
体調管理も含め、リスク管理は本当に重要ですね！

＜本日の題材＞
日付関数についての続き（年齢計算）

システムでは、生年月日をデータベースの項目に持つテーブルが存在するケースが非常に多いですね。日付の計算に関連して、今回は年齢の計算について見てみたいと思います。

データベースがOracleであれ、SQL Server であれ、年齢計算においては、以下の2つの内容を確認することになるかと思います。
１）生年月日と基準日(通常は今日)の「年」の差
２）生年月日と基準日(通常は今日)の「月日」の大小を比較し、基準日が誕生日に達してなければ -1

SQLとしてはいろいろなやり方があると思いますが、以下に例を挙げます。

Oracle：
SELECT
EXTRACT(YEAR FROM 基準日) - EXTRACT(YEAR FROM 誕生日) -CASE WHEN TO_CHAR(基準日, 'MMDD') < TO_CHAR(誕生日, 'MMDD') THEN 1 ELSE 0 END AS 年齢
FROM テーブル名 ;

SQL Server：
SELECT YEAR(基準日) - YEAR(生年月日)-
CASE WHEN RIGHT(CONVERT(VARCHAR , 基準日, 112), 4) < RIGHT(CONVERT(VARCHAR , 生年月日, 112), 4) THEN 1 ELSE 0 END AS 年齢
FROM テーブル名 ;

※YEAR(基準日) - YEAR(生年月日) は、DATEDIFF(YEAR,生年月日,GETDATE())でも同じ

例）
SELECT 社員コード
, 続柄
, CONVERT(VARCHAR,生年月日,111) AS 生年月日
, YEAR(GETDATE()) - YEAR(生年月日)-
CASE WHEN RIGHT(CONVERT(VARCHAR , getdate(), 112), 4) < RIGHT(CONVERT(VARCHAR , 生年月日, 112), 4) THEN 1 ELSE 0 END AS 年齢
FROM dbo.D社員家族
ORDER BY 社員コード, 生年月日;

また、簡単に計算する方法として、日付型(yyyymmdd)を8桁数値に変換後に10000で割って小数点を切り捨てる方法もよく使われるようです。

Oracle：
SELECT TRUNC((TO_CHAR(基準日,'YYYYMMDD') - TO_CHAR(生年月日, 'YYYYMMDD')) /10000, 0) AS 年齢 FROM テーブル名;

※TRUNC関数は切り捨てで、第2引数が少数第何位での切り捨てかを示す。

SQL Server：
SELECT
ROUND((CONVERT(INT,CONVERT(VARCHAR(8),基準日,112))-CONVERT(INT,CONVERT(VARCHAR(8),生年月日,112))/10000,0,1) AS 年齢
FROM テーブル名;

※ROUND関数の第3引数が0以外の場合は切り捨てになります。また、FLOOR関数を使用すると、引数に対して、それ以下で最も大きい整数値を取るので同じことになります。
※閏年生まれの方も、3月1日時点できちんと年齢が上がります。

例）
SELECT 社員コード
, 続柄
, CONVERT(VARCHAR,生年月日,111) AS 生年月日
, ROUND((CONVERT(INT,CONVERT(VARCHAR,getdate(),112))-CONVERT(INT,CONVERT(VARCHAR,生年月日,112)))/10000,0,1) AS 年齢
FROM dbo.D社員家族
ORDER BY 社員コード, 生年月日;

結果は最初の処理と同じ。

注意）日本の法律（年齢計算に関する法律）上は、年齢というのは誕生日の前日で加算されるもののため、計算結果の利用目的によっては上記のような計算に1日加算するなど調整が必要になります。年齢を元に何らかの計算するような場合、年齢計算を法律に則って行うのか、一般慣習に基づいて行っていいのかよく調査してから計算式を決定する必要があります。

★また、EXCELからデータをアクセスできる環境であれば、EXCELのほうで年齢計算をできる（DATEDIF関数）ので、EXCEL側で年齢を表示させることが簡単にできます。

【書式】
DATEDIF(開始日,終了日,単位)
【単位の引数】
"Y" 期間内の満年数
"M" 期間内の満月数
"D" 期間内の日数
"MD" 開始日から終了日までの日数
"YM" 開始日から終了日までの月数
"YD" 開始日から終了日までの日数

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

先週末の雨と風で桜が結構散ってしまいましたね。これはその前に撮った家の近くの庁舎にある桜の写真です。

４月に入り、子供たちもいよいよ始業式、入学式の時期になりました。わが家も子供たちが学校へ行く準備で慌ただしくしている朝の日々が戻ってきました。

朝になって、子供たちからあれがない、これがない、これを準備して！とか言われると本当に大変ですよね。前の日の夜に次の日の準備をしっかりさせるように躾けることが大事だとつくづく感じます。

＜本日の題材＞ 日付関数についての続き

前回、日付型関数について少し話をしましたが、日付の計算などについて今回は見てみたいと思います。

①    何日後・何日前など、日にちを足し引きする場合：
Oracle：
　DATE型の項目に対して、足し引きしたい日数を単純にプラス 、マイナスすればよい。

SQL Server：
  DATEADD関数　(構文：DATEADD(datepartパラメータ, number, date) )でパラメータに dayを使用する。

※追記）
Oracleと同様に、足し引きしたい日数をプラスマイナスも可能です。ただし、Oracleの場合は、日数だけでなく、時間・分・秒なども 1/24 、1/24/60、1/24/60/60 などを足し引きすることで計算が可能ですが、SQL Serverでは日数までです。

例）購入日(DATE型)の7日後、7日前
Oracle：
    SELECT 購入日
　　　　　　,　購入日+7  AS 購入日の7日後
　　　　　　,　購入日-7  AS 購入日の7日前
      FROM テーブル名;

購入日が ’2014-03-25’ だった場合、結果は以下のようになる：
　　　購入日         　　　購入日の7日後    購入日の7日前
　　　-----------------      -------------------     --------------------
　　　14-03-25       　　14-04-01          　14-03-18

SQL Server：
    SELECT  購入日
　　　　　　,  DATEADD(day, 7, 購入日)  AS 購入日の7日後
　　　　　　,  DATEADD(day, -7, 購入日)  AS 購入日の7日前
      FROM テーブル名;

※Oracleと同様の、+7、-7 などの計算も可能です。

 
②    何か月後・何か月前など、月数を足し引きする場合：
Oracle：
ADD_MONTHS関数(構文：ADD_MONTHS (date型項目, integer) )。

SQL Server：
  DATEADD関数(構文：DATEADD(datepartパラメータ, number, date型項目) )でパラメータに month を使用する。

例）購入日(DATE型)の月末日の１ヶ月後、１ヶ月前
Oracle：
  SELECT   LAST_DAY(購入日)  AS 購入日の月末日
　,  LAST_DAY(ADD_MONTHS(購入日,1))  AS 購入日の翌月末日
　,  LAST_DAY(ADD_MONTHS(購入日,-1))  AS 購入日の前月末日 　FROM テーブル名;

   購入日が ’2014-03-25’ だった場合、結果は以下のようになる：
　    購入日の月末日     購入日の翌月末日     購入日の前月末日
       --------------------      ----------------------         -----------------
       14-03-31                  14-04-30                        14-02-28

SQL Server (2012)：
    SELECT      EOMONTH(購入日)  AS 購入日の月末日
  ,  EOMONTH(DATEADD(month, 1,購入日))  AS 購入日の翌月末日
  ,  EOMONTH(DATEADD(month, -1,購入日))  AS 購入日の前月末日     FROM テーブル名;

※ただし、上記の月末日を取得する EOMONTH関数は、SQL Server2012から使える機能です。それ以前の場合には、翌月１日から１日引いた日付を取得するかたちが一般的ですね。いろいろな書き方があると思いますが、例えば以下のような文で今月の月末日を抽出できます。

SELECT DATEADD(DAY,-1,DATENAME(YEAR,DATEADD(MONTH,+1,GETDATE())) + '-' + DATENAME(MONTH,DATEADD(MONTH,+1,GETDATE())) + '-' + '01') as 当月末

★前回と同様、EXCELからデータをアクセスできる環境であれば、EXCELのほうで月末日を取得する関数（EOMONTH関数）があるので、EXCEL側で月末日を表示させることが簡単にできます。

今日は以上まで

 

にほんブログ村

ITコーディネータのシュウです。

会社の近くで撮った写真、桜が見事に咲きました！
遠方に見えるのは、さいたま新都心のビル群

WindowsXPのサポート期限切れが迫ってきました。4月9日です。Office2003も同一日でサポートが切れますね。

先日(3/6~7)、東京駅のすぐ隣のＪＰタワーで行われたSecurity Days 2014でもそのことを取り上げて、セキュリティに注意を促すセッションを行うところも結構ありました。日本MSの発表では、XPとWindows8.1ではウィルス感染率に21倍もの差があるとか。。。

昨年の11月の調査時点で、サポート終了予定から約1年が経過した2015年3月時点でも、18.2％のPCがWindows XPで稼動予定という矢野経済研究所の調査報告(http://enterprisezine.jp/article/detail/5403)があり、総務省の2013/11/22の発表では自治体でも26万6千台が期限切れになるという予想報告や、最近でも個人でXPを使用している人がまだ4人に一人という調査結果を出しているところもあります。

問題は、サポートが切れることで、新しく発見された脆弱性に対してXPについてはセキュリティパッチが提供されなくなるため、その脆弱性を狙った攻撃があればそれによる被害が発生してしまう危険性が高いということです。最近は標的型攻撃とよばれるサイバー攻撃が非常に増えてきているということなので、それに対応した防御も十分考える必要が出てきていますね。

＜本日の題材＞
日付関数について

前回、日付型にどういうものがあるかという話をしましたが、日付型項目の値を表現したり計算したりする場合に、関数を使用するケースが随所に出てきます。その関数についても、OracleとSQL Serverでは違いがあるので、整理しておくと便利かと思います。

まず、よく使うのがシステム日付。

Oracle：SYSDATE
SQL Server：GETDATE()

実際にSQLでシステム日付を確認するのは、
Oracleでは、
SELECT SYSDATE FROM dual;
SQL Serverでは、
SELECT GETDATE();

そして、日付型項目を日本語の日付表現に直して表すとき、西暦の4桁の年月日までか、年月までか、年だけの表記にするか、それとも時刻まですべて表現するのかなど、いろいろとシステムによって項目ごとに使い分ける場合があると思います。

今回は、この日付のフォーマットに関して少し上げてみたいと思います。

Oracleの場合は、TO_CHAR関数を使用して、フォーマットを設定しますが、SQL Serverの場合は、CONVERT関数を利用することが多いと思います。

例）
①yyyy/mm/dd という表示にしたい
Oracle：
SELECT TO_CHAR(日付項目, ‘YYYY/MM/DD’) FROM テーブル名;
SQL Server：
SELECT CONVERT(VARCHAR, 日付項目, 111) FROM テーブル名;

②yyyy-mm-dd hh:mi:ss というように日付+時刻(24時間表記)で表示したい
Oracle：
SELECT TO_CHAR(日付項目, ‘YYYY-MM-DD HH24:MI:SS’) FROM テーブル名;

SQL Server：
SELECT CONVERT(VARCHAR, 日付項目, 120) FROM テーブル名;

結果はこんな感じ：
2014-03-31 14:46:39

③和暦で表示したい
Oracle：
SELECT TO_CHAR(日付項目, 'EEYY"年"mm"月"dd"日"', 'nls_calendar = ''Japanese Imperial''') 日付 FROM テーブル名;

結果はこんな感じ：
日付
----------------------------
平成26年3月31日

※Oracleの書式について
E ： 元号の略語（大正:T、昭和:S、平成:H）
EE： 元号（大正、昭和、平成）

和暦変換する場合は'nls_calendar = ''Japanese Imperial'''で「nls_calendarオプション」のカレンダを指定するかたちになります。

SQL Server：
SQL Serverでは、実は和暦に変換する関数は持っていないため、SQLを駆使してこれを実現するケースが多いと思います。例えば元号を表すためには

SELECT
Case when 日付項目 > '19890107' then '平成'
when日付項目 > '19261224' then '昭和'
when日付項目 > '19120729' then '大正'
when日付項目 > '18680124' then '明治'
else 'その他' end 年号
というようなかたちでSQLで設定が必要です。

ただし、例えば結果をEXCELで表示するというように、EXCELからデータをアクセスできる環境であれば、EXCELのほうで日付データに対して和暦の表示を行うことができるので、SQL Serverのデータでも、簡単に和暦に変換することが可能になります。

EXCELはいろいろと関数を持っているので、使えば結構便利ですよね。

なお、SQL ServerのCONVERT関数についての仕様やパラメータについては、以下のサイトで確認できます。
http://msdn.microsoft.com/ja-jp/library/ms187928.aspx

今日は以上まで

にほんブログ村

ITコーディネータのシュウです。

ソチオリンピック、パラリンピックが終わってしばらくたちましたが、まだ感動が残っていますね。

羽生選手のように、力を存分に発揮できた人もいれば、練習してきた成果を完全には発揮できなかった人もいましたが、浅田選手のように、前半の失敗で絶望のような状況になりながらも、気持ちを切り替えて後半は全てを出し切って表現し、皆に深い感動を与えてくれた選手もいました。最後笑顔で終えることができて、本当によかったと思います。日本中の人たちがそう思ったでしょう。ジャンプ団体も夜中ずっとテレビで応援してましたが、インタビューにも感動しました。

オリンピックに出ること自体がとても大変なことでしょうが、本番で自分の力を出し切ることはどれほど難しいことかと思います。しかし、それぞれに見るものに感動を与える力があり、やっぱりスポーツとはすばらしいですね。選手一人ひとりに拍手を送りたいです。

＜本日の題材＞ 【日付型について】

日付・時刻に関する型は、

Oracle：
　　date、timestamp、timestamp with time zone

SQL Server：
 　datetime、smalldatetime
                                    -- SQL Server2005まで
 　date、time、datetime2、datetimeoffset
                                    -- SQL Server2008から追加
などがあります。

私も過去、システム開発でよく使用していたのは、Oracleでは「date」型、SQL Serverでは「datetime」型（どちらも、日付と時刻の両方の値を保持）だったのですが、他にも上記のような型が存在しています。

SQL Serverで「datetime」型を使用していた時に、たまたま登録しようとしたデータに 1753/1/1 よりも古い値が存在していたときに、エラーになったことがあり、そんな制限があるんだとびっくりしたことがありました。

ちなみに、エラーメッセージはこんな感じ：

メッセージ 242、レベル 16、状態 3、行 3 varchar データ型から datetime データ型への変換の結果、範囲外の値になりました。

なぜ 1753 年から開始なのか？　それは datetime 型が「グレゴリオ暦」であることに起因しているようで、当時のイギリス帝国 (とその植民地) でグレゴリオ暦を採用した年が1752 年であり、通年としてグレゴリオ暦が始まったのが 1753 年からだからとのこと。 （ちなみに Oracleの「date」型は -4712/01/01 ～ 9999/12/31 までの範囲）

SQL Server2008からは、新しく 「datetime2」型：西暦1/1/1以降のデータを扱えるもの、 「date」型： 時刻の部分はなく日付部分のみをデータとして持つもの、 「time」型： 時刻の部分のみ持ち、時間も100ナノ秒まで扱えるもの などが増えています。

これに対して、Oracleも「timestamp」型というもので、日付＋時刻で最少ナノ秒単位まで扱える型があります。

また、タイムゾーン・オフセットを考慮した型もあり、下記のようなものがあります。

Oracle：timestamp with time zone、timestamp with local time zone SQL Server：datetimeoffset

タイムゾーン・オフセットとは、ローカル時間とUTC（協定世界時、以前のグリニッジ標準時）との時差（時間および分単位）になり、日本の標準時間はグリニッジ標準時に9時間プラスした時間となります。

どういうときにこの型を使うのか？というと、グローバルに展開する企業のシステムなどで、時差がある国や地域で作成した部品を組み立てて製品を完成しユーザに届けるような場合、各地の時刻で日付を登録するとわかりにくくなるところを、全てある地域の時刻に変換して表現することで、システムとしてわかりやすくするような場合など、この型が必要な場合があるようです。

日付関数などについて、続きは次回

 

にほんブログ村