SQL Server 民國日期時間 數字轉datetime | SQL Server Taiwan Calander date and time number to datetime

markdown ``` --原始欄位: --民國年數字date_number,時間數字time_number --(ex:971201,6) --將民國數字轉西元字串 select cast(970101+19110000 as varchar(8)) --output:20080101 --將西元字串轉YYYY-MM-DD字串 select SUBSTRING(cast(970101+19110000 as varchar(8)),1,4)+'-' +SUBSTRING(cast(970101+19110000 as varchar(8)),5,2)+'-' +SUBSTRING(cast(970101+19110000 as varchar(8)),7,2) --output:2008-01-01 --將時分數字PAD成時分秒，在零點時可能只有個位數 如 00:06 =6 select REPLACE(STR(cast(6 as varchar(6)), 6), SPACE(1), '0') --output:000006 --將補齊的時分秒轉為HH:MM:SS字串 select substring(REPLACE(STR(cast(6 as varchar(6)), 6), SPACE(1), '0') ,1,2)+':' +substring(REPLACE(STR(cast(6 as varchar(6)), 6), SPACE(1), '0') ,3,2)+':' +substring(REPLACE(STR(cast(6 as varchar(6)), 6), SPACE(1), '0') ,5,2) --output:00:00:06 --將時間字串與時分秒字串轉為DATETIME select convert(datetime, SUBSTRING(cast(970101+19110000 as varchar(8)),1,4)+'-' +SUBSTRING(cast(970101+19110000 as varchar(8)),5,2)+'-' +SUBSTRING(cast(970101+19110000 as varchar(8)),7,2) +' ' +substring(REPLACE(STR(cast(6 as varchar(6)), 6), SPACE(1), '0') ,1,2)+':' +substring(REPLACE(STR(cast(6 as varchar(6)), 6), SPACE(1), '0') ,3,2)+':' +substring(REPLACE(STR(cast(6 as varchar(6)), 6), SPACE(1), '0') ,5,2) ,121) --output:2008-01-01 00:00:06.000 ```