[SQL]利用CONVERT來轉換yyyy/mm/dd日期格式

日期格式 sql

box9229 2021-01-27 14:50:52 ‧ 7477 瀏覽

SQL欄位的日期格式有些是 "20200127"
少部份格式被新程式改成是 "2021-01-27"

己經查到要用這方法
select CONVERT(varchar(12) , getdate(), 112 )
上面是把今天日期設定成"20210127"

要如何取出欄位 ci004 ci005 ci006 的日期
格式改過來，然後原日期存回去

例如
日期 "2000-01-01" 至 "2024-12-31"
改成 "20200101" ~ "20241321"

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

純真的人 iT邦大師 1 級 ‧ 2021-01-27 14:55:28 檢舉

你弄個範例來吧~
ci004 ci005 ci006 什摩東西..
20241321 我都不知道有超過12月的月份...

froce iT邦大師 1 級 ‧ 2021-01-27 14:55:53 檢舉

ci004 ci005 ci006 這三個欄位是啥？年月日？

為啥都不用date去存啊？

box9229 iT邦新手 4 級 ‧ 2021-01-27 14:59:09 檢舉

謝謝回答
欄位名稱是 ci004 內容是 “2024-12-31”
要改成 "20241231”
打錯了

純真的人 iT邦大師 1 級 ‧ 2021-01-27 15:04:51 檢舉

但你沒有說ci004 ci005 ci006 的欄位是varchar類型回存?還是date類型?
因為要回存ci004 欄位的內容變成20241231 ，那你的ci004 欄位類型是varchar吧?

box9229 iT邦新手 4 級 ‧ 2021-01-27 17:06:09 檢舉

UPDATE tablename set ci004=REPLACE(ci004,'-','') from tablename WHERE ci004 LIKE '%-%'

box9229 iT邦新手 4 級 ‧ 2021-01-27 17:07:02 檢舉

OK 了
謝謝大家的回答

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

4 個回答

rogeryao

iT邦超人 8 級 ‧ 2021-01-27 15:04:44

最佳解答

CREATE TABLE A (
ci004 varchar(40) NULL ,
ci005 varchar(40) NULL ,
ci006 varchar(40) NULL);

INSERT INTO A (ci004,ci005,ci006)
VALUES 
('20200127','20200128','20200129'),
('2021-01-27','2021-01-28','2021-01-29'),
('2022-01-27','20220128','20230129');

UPDATE X
SET ci004=REPLACE(ci004,'-',''),
ci005=REPLACE(ci005,'-',''),
ci006=REPLACE(ci006,'-','')
FROM A AS X
WHERE ci004 LIKE '%-%' OR ci005 LIKE '%-%' OR ci006 LIKE '%-%'

Demo

回應 3
分享
檢舉

純真的人 iT邦大師 1 級 ‧ 2021-01-27 15:18:30 檢舉

其實不能直接更新~會有問題的~
因為欄位內容如果是2021-1-1會變成202111少了2個字元~
他以後要查詢區間就會錯誤...

rogeryao iT邦超人 8 級 ‧ 2021-01-27 15:22:25 檢舉

2021-1-1 是有可能的...

box9229 iT邦新手 4 級 ‧ 2021-01-27 17:16:26 檢舉

UPDATE tablename set ci004=REPLACE(ci004,'-','') from tablename WHERE ci004 LIKE '%-%'
剛剛指令下完的結果
20200831 月日會自動補0
那個 DEMO 網站真好用

登入發表回應

allenlwh

iT邦高手 1 級 ‧ 2021-01-27 14:55:46

declare @olddate varchar(10)
set @olddate='2021-01-27'
select replace(@olddate,'-','')

回應 2
分享
檢舉

allenlwh iT邦高手 1 級 ‧ 2021-01-27 14:57:28 檢舉

declare @olddate varchar(10)
set @olddate='2021-01-27'
select convert(varchar(10),convert(datetime,@olddate),112)

box9229 iT邦新手 4 級 ‧ 2021-01-27 17:19:07 檢舉

最後用REPLACE 解決
謝謝了

登入發表回應

㊣浩瀚星空㊣

iT邦大神 1 級 ‧ 2021-01-27 15:04:37

假設你的欄位格式都是字串。

第一招，直接格式更新法：

UPDATE db SET
ci004  = CONVERT(varchar(12) , ci004, 112 )
WHERE ci004 like '%-%'

第二招：消除法：

UPDATE db SET
ci004  = replace(ci004 , '-', '' )

第三招，時間辨識轉換法：
不過這招看有沒有人會寫。因為我sql的函式不熟，
需要將字串辨識後轉成unixtime後(如php的strtimtime函式)。再重新轉換日期格式。
只是這招比較常用在時區轉換用就是了。

回應 3
分享
檢舉

純真的人 iT邦大師 1 級 ‧ 2021-01-27 15:17:35 檢舉

其實不能直接更新~會有問題的~
因為欄位內容如果是2021-1-1會變成202111少了2個字元~

㊣浩瀚星空㊣ iT邦大神 1 級 ‧ 2021-01-27 16:08:28 檢舉

這也的確要考量進去。
我以前是比較喜歡用第三招。

只要是能辨識的日期格式統統轉換成unixtime是最安全的。
遇到不認識的了不起就是0了。

只是不清楚sql是否有這樣的函式能用就是了。理論上因該要用才對。

box9229 iT邦新手 4 級 ‧ 2021-01-27 17:07:55 檢舉

UPDATE tablename set ci004=REPLACE(ci004,'-','') from tablename WHERE ci004 LIKE '%-%'
OK 了
謝謝大家的回答

登入發表回應

純真的人

iT邦大師 1 級 ‧ 2021-01-27 15:14:20

恩~你參考看看吧

declare @Test table(
	ci004 varchar(10)
	,ci005 varchar(10)
	,ci006 varchar(10)
)

insert into @Test
values('','2020-12-31','')
,('2000-12-1','','2000-12-31')
,('','2020-1-1','2020-12-31')

update @Test
set ci004 = (case when isDate(ci004) = 1 then CONVERT(varchar(12),Convert(date,ci004), 112 ) else ci004 end)
, ci005 = (case when isDate(ci005) = 1 then CONVERT(varchar(12),Convert(date,ci005), 112 ) else ci005 end)
, ci006 = (case when isDate(ci006) = 1 then CONVERT(varchar(12),Convert(date,ci006), 112 ) else ci006 end)

select *
from @Test