紀錄一下在 SQL 指令中用到的一些語法
資料庫範例使用本機的北風資料庫--Northwind
SELECT * FROM [Northwind].[dbo].[Customers]
(1) SELECT 後的值加序號
select row_number() over(order by [CompanyName]) as Name,* from [Northwind].[dbo].[Customers]
(2) 序號前補零(三位元)
select substring('000', 1, 3 - len(ROW_NUMBER() OVER(ORDER BY [CompanyName]))) +
convert(char(3), ROW_NUMBER() OVER(ORDER BY [CompanyName])) AS Number ,* from [Northwind].[dbo].[Customers]
(3) 四捨五入
SELECT ROUND(21.3,1) AS DC021
(4) 去除多餘的小數點
SELECT cast (0.540000 as float) FF
(5) 取得系統時間
Select Getdate()
(6)當日凌晨
select convert(datetime, convert(varchar(20), getdate(), 101)) as ShowDate
(7)本月第一天
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) as ShowDate
(8)上個月最後一天
select DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)) as ShowDate
(9)本月最後一天
select DATEADD(mm, 1, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))) as ShowDate
