文字列型で保存された日付データを効率よく日付型に変換するには

oracle 10gで
varcharのカラムに日付と時刻が以下のように保存されているのですが、
これをsubstrで文字を区切って日付型に変換しております。

↓カラムに保存されている文字列データ
20161115 001024000

↓日付型に変換

SQL
1TO_DATE(
2		SUBSTR(TABLE1.WORK_DATETIME,1,4)  || '-' ||
3		SUBSTR(TABLE1.WORK_DATETIME,5,2)  || '-' ||
4		SUBSTR(TABLE1.WORK_DATETIME,7,2)  || ' ' ||
5		SUBSTR(TABLE1.WORK_DATETIME,10,2)  || ':' ||
6		SUBSTR(TABLE1.WORK_DATETIME,12,2)  || ':' ||
7		SUBSTR(TABLE1.WORK_DATETIME,14,2) ,
8		'YYYY-MM-DD HH24:MI:SS' ) AS WORK_DATETIME

長いっ！！！
どうにかもっとSQLを短く、そして処理速度が早くできる方法はないでしょうか？
いまのところこのままストアドプロシージャを作成しようかと思っているのですが、なにか良いアイデアがあればよろしくお願いします。

行動規範の内容に同意します

回答3件

ベストアンサー

以下を参考に。
SQL> SELECT TO_DATE('2016/11/15 08:45:46', 'YYYY/MM/DD HH24:MI:SS') AS DATE1 FROM DUAL ;

投稿2016/11/14 23:47

Orlofsky

総合スコア16417

jm1156

2016/11/14 23:53

TO_DATE(TABLE1.WORK_DATETIME, 'YYYY/MM/DD HH24:MI:SS') AS DATE1 と、やってみたら、「ORA-01861:リテラルが書式文字列と一致しません」とでます…。文字列のほうにスラッシュやセミコロンがないとダメなかんじでしょうか？

Orlofsky

2016/11/15 00:09

そうです。そう思ったら質問の前に実際にSQLを実行して動作確認しましょう。また、マニュアルも確認する習慣は身に着けてください。ありがたいことに無料で公開されています。 TO_DATE http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/functions.html#91059 日時書式モデル http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/sql_elements.html#44928 Oracle SQL入門のやさしい本を1冊買って来て勉強するも良し。

jm1156

2016/11/15 00:15

ありがとうございます。そう思って作成したのが上記のSQLなのですが、長いのでもう少し短く効率のよい書き方はないものかと思った次第です。

Orlofsky

2016/11/15 00:32

上記URLの書式モデルはざっと目を通しておくと良いですよ。 SQL 入門、SQL 関数、擬似列、SQL Tips http://www.shift-the-oracle.com/sql/

jm1156

2016/11/15 00:59

ありがとうございます。目は通していたつもりなのですが、私の目が節穴だったようです。

行動規範の内容に同意します

TO_DATEだと秒より下が扱えないので、おっしゃるようにミリ秒部分をSUBSTR等で取り除くしかないですね。
TO_TIMESTAMPならそのままいけますが、TIMESTAMP型になってしまうので要件を満たさないかも？

SELECT TO_TIMESTAMP('20161115 001024000','YYYYMMDD HH24MISSFF3') FROM DUAL;

投稿2016/11/15 01:29

編集2016/11/15 01:34

alg

総合スコア2019

jm1156

2016/11/15 04:15

そういいことですね。 TIMESTAMPかDATEかは顧客の仕様を確認します。ありがとうございました。

Orlofsky

2016/11/15 04:25 編集

DATE型は秒までのデータしか保持できないので、秒未満のデータも必要ならTIMESTAMP型にする必要があります。 DATE型とTIMESTAMP型ではテーブル設計も変わるし、お客様で用意してあるか、これから作るFUNCTIONのDATE型の引数もTIMESTAMP型で必要になりますから、オーバーロードで用意したりしないといけないので、お客様には大急ぎできちんと確認してください。

行動規範の内容に同意します

SQL
1SELECT TO_DATE('20161231 123456', 'YYYYMMDD HH24MISS')
2FROM DUAL

普通にこうじゃないか

投稿2016/11/15 00:35

退会済みユーザー

総合スコア0

jm1156

2016/11/15 00:57

回答ありがとうございます。自己解決しました。どうやら私の文字列データには「ミリ秒」まではいっていたので、 TO_DATE(TABLE1.WORK_DATETIME, 'YYYYMMDD HH24MISS')　だとエラーになっていました。 TO_DATE(SUBSTR(TABLE1.WORK_DATETIME,1,15) ,'YYYY-MM-DD HH24:MI:SS' ) で、スッキリいけました。

行動規範の内容に同意します

あなたの回答