iT邦幫忙

1

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

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

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

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

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

看更多先前的討論...收起先前的討論...
你弄個範例來吧~
ci004 ci005 ci006 什摩東西..
20241321 我都不知道有超過12月的月份...
froce iT邦大師 1 級 ‧ 2021-01-27 14:55:53 檢舉
ci004 ci005 ci006 這三個欄位是啥?年月日?

為啥都不用date去存啊?
box9229 iT邦新手 5 級 ‧ 2021-01-27 14:59:09 檢舉
謝謝回答
欄位名稱是 ci004 內容是 “2024-12-31”
要改成 "20241231”
打錯了
但你沒有說ci004 ci005 ci006 的欄位是varchar類型回存?還是date類型?
因為要回存ci004 欄位的內容變成20241231 ,那你的ci004 欄位類型是varchar吧?
box9229 iT邦新手 5 級 ‧ 2021-01-27 17:06:09 檢舉
UPDATE tablename set ci004=REPLACE(ci004,'-','') from tablename WHERE ci004 LIKE '%-%'
box9229 iT邦新手 5 級 ‧ 2021-01-27 17:07:02 檢舉
OK 了
謝謝大家的回答
1
rogeryao
iT邦大師 5 級 ‧ 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

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

rogeryao iT邦大師 5 級 ‧ 2021-01-27 15:22:25 檢舉

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

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

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

0
allenlwh
iT邦好手 1 級 ‧ 2021-01-27 14:55:46
declare @olddate varchar(10)
set @olddate='2021-01-27'
select replace(@olddate,'-','')
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邦新手 5 級 ‧ 2021-01-27 17:19:07 檢舉

最後用REPLACE 解決
謝謝了

2

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

第一招,直接格式更新法:

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

第二招:消除法:

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

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

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

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

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

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

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

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

0
純真的人
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

https://ithelp.ithome.com.tw/upload/images/20210127/20061369amsjgYkLNY.png

box9229 iT邦新手 5 級 ‧ 2021-01-27 17:11:10 檢舉

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

20200831 月 日會自動補0
OK 了
謝謝大家的回答

我要發表回答

立即登入回答