SQL西元年轉民國年百位格式問題(民國年轉年齡困難)

sql oracle

shawnchill717 2022-07-06 10:15:40 ‧ 4637 瀏覽

分享至

最近在做SQL的日期格式轉換
不過遇到一個問題
當我在轉換時必須先指定好格式
但由於我的資料有民國十位數與百位數
如果我轉出的值為民國百位數以下
就顯示yy/mm/dd格式即可前面不要0
如果為百位數以上就顯示yyy/mm/dd
網上的資源都沒有教如何做這方面的條件排除
想問各位有何方法
這邊-697979天跟西元年-1911一樣的意思

更新
現在要做民國年轉西元年
不過我的民國年格式欄位為VARCHAR
並且格式為yyy/mm/dd
在這情況之下，怎麼樣正確地將
111/07/06 轉為 2022/07/06呢?

感謝嗯凹屋ㄟ歐的建議
但是由於我的欄位為varchar
並且內容有這個/斜線符號
所以在使用TO_DATE轉型時會報錯

透過TO_CHAR也會出現此報錯(發現是潤年問題)

更新-如果想透過民國年去算出年齡該怎麼做呢?
西元年可以透過sysdate去-西元出生年即可
但是若是民國年的話，是否還需先將sysdate轉成民國年，再來做計算呢?
目前可透過西元年查出年齡

目前用民國年轉sysdate後再計算年齡發現問題

但明明一般sysdate轉民國的時候沒問題呀
但轉完後計算年齡就出錯了

看更多先前的討論...收起先前的討論...

shawnchill717 iT邦新手 4 級 ‧ 2022-07-06 11:01:34 檢舉

另外想問，民國年要轉西元年的話有時麼方法呢
我的格式為2022/07/06
要轉成111/07/06

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2022-07-06 11:35:01 檢舉

試看看 TO_CHAR(SYSDATE - INTERVAL '1911' YEAR(4) , 'fmYYYY/MM/DD' )

shawnchill717 iT邦新手 4 級 ‧ 2022-07-06 11:51:26 檢舉

這裡遇到一個困難
由於我存民國年birthday的欄位為varchar
所以好像沒辦法這樣用

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2022-07-06 12:03:58 檢舉

先做TO_DATE呀 TO_DATE(LPAD('1110101',8,'0'),'YYYYMMDD') 轉成正確的日期應該就可以隨便弄了?

shawnchill717 iT邦新手 4 級 ‧ 2022-07-06 12:25:26 檢舉

由於我的格式為varchar, 並且值的格式為 yy/mm/dd 或是 yyy/mm/dd
這種情況不知道能不能正確使用TO_DATE?

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2022-07-06 12:39:34 檢舉

要寫滿yyyy, 在有分隔符號的狀態下oracle還蠻聰明的, 相關可以查看看RRRR跟YYYY的差別

窮嘶發發發 iT邦高手 1 級 ‧ 2022-07-06 13:36:23 檢舉

這邊有飯粒，快吃
http://blog.sqlqna.net/2018/01/blog-post_23.html
還可以餵狗
https://www.google.com/search?q=oracle+民國年

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2022-07-06 13:45:06 檢舉

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=7f6f0dd0d62a41eb7f1e3f537e038097

shawnchill717 iT邦新手 4 級 ‧ 2022-07-06 16:36:32 檢舉

@喵凹咿唉思嗯
我執行後會出現報錯 ORA-01839: 此一日期對所設定的月份而言是無效的

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2022-07-06 17:09:37 檢舉

那就是你有髒資料呀..大概是有人填了英式格式吧..用文字格式去存日期是個很不好的做法, 要先把髒資料找出來先

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2022-07-06 17:19:42 檢舉

ah.. 還有個可能因為潤年的關係 XDDD

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2022-07-06 17:30:26 檢舉

如果考慮潤年問題的話可能重組會比較安全
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=6e82a342dfe2c119dc20f5ffba68859f

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2022-07-06 17:38:56 檢舉

或是試看看發發發給的範例吧, 裡面有指定日曆轉換的範例

窮嘶發發發 iT邦高手 1 級 ‧ 2022-07-07 09:04:46 檢舉

民國年是小問題，我聽過有傳統公司只幫員工過農曆生日
那個要轉換跟計算，呵呵呵，聽到就是很麻煩
那也是寫入西元生日之後轉換的，ORACEL本身沒有函數可以直接轉
真的很麻煩，不知道經手的大神是怎麼搞出來的

shawnchill717 iT邦新手 4 級 ‧ 2022-07-07 09:09:15 檢舉

想請問，還有一個問題
如果想透過民國年去算出年齡的話，該怎麼做呢?
目前可以透過西元年轉得出年齡，但民國年看來有點困難..
不然就要寫很長的SQL

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2022-07-07 10:01:52 檢舉

農曆呀..聽起來就頭痛... 我的話大概會想辦法去把google的弄下來再map上去XD 算年齡看有沒要精確吧如果只是用年份來減應該挺快的, 要轉成西元年再算也行呀..應該不會長到哪去?

shawnchill717 iT邦新手 4 級 ‧ 2022-07-07 10:44:26 檢舉

喵大能再幫我看看我寫得有什麼錯嗎
看來是資料型態出錯，但包了一層TO_DATE
也會報錯(無效的數字)><

shawnchill717 iT邦新手 4 級 ‧ 2022-07-07 10:49:22 檢舉

看來是months_between只接受date格式?

喵凹咿唉思嗯 iT邦研究生 5 級 ‧ 2022-07-07 15:25:10 檢舉

你還是乖乖用日曆檔吧.. 減少對資料的操作對你現下的狀態比較安全
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=bee82bc790c966651d5d7bac0f9c8cca

nolem iT邦新手 5 級 ‧ 2024-11-11 20:15:07 檢舉

如果民國年格式是 099/07/06或是99/07/06 或是 111/07/06 之類用以下進行解析轉換即可
select to_date(to_char(to_number(substrb(:chdate,1,instrb(ltrim(:chdate,'0'),'/')-1))+1911) ||'/' || substrb(:chdate,instrb(ltrim(:chdate,'0'),'/')+1),'yyyy/mm/dd') AD_DATE from duall;
這樣就可以轉回西元年了,再拿他去運算

登入發表討論