數學運算式或搭配系統函式

Use Northwind

/*
使用運算式
*/

--計算每個訂單明細的小計
Select OrderID,ProductID,UnitPrice*Quantity*(1-Discount) as N'小計'
From dbo.[Order Details]

/*數學運算子*/
select 10/3 as '商數',10%3 AS '餘數'

/*Date and time Function*/
--1.Getdate(),GetUTCDate() --format for datetime values
select Getdate() as '現在時間'
,GetUTCDate() as '標準時間'

--2.Year()/Month()/Day() --Returns an integer
Select Year(GetDate())-1911,
Month(Getdate()),
Day(Getdate())
--- Convert to varchar
Select Convert(Nvarchar,Year(GetDate())-1911)+N'年'+
Convert(Nvarchar,Month(Getdate()))+N'月'+
Convert(Nvarchar,Day(Getdate()))+N'日' as N'民國'
--- style
select Getdate() as '現在時間',
Convert(varchar,getdate(),111) as 'JAPAN',
Convert(varchar,getdate(),101) as 'US'

--3.Datename()/Datepart()
--Datepart() Returns an integer
--Datename() Returns a character string
Select Year(Getdate()),DatePart(year,getdate()),DateName(year,getdate())
Select Datepart(Month,Getdate()),
Datepart(WeekDay,Getdate()),
DateName(Month,Getdate()),
DateName(WeekDay,Getdate())
--列出語系
select * from sys.syslanguages
--列出本連線使用語系
select @@language
--設定連線使用語系
set language N'繁體中文' --name
Select DateName(Month,Getdate()),
DateName(WeekDay,Getdate())
--set language N'Français'
select Month('10/11/12'),
Convert(datetime,'10/11/12')
set DateFormat 'ymd'
select Month('10/11/12'),
Convert(datetime,'10/11/12')
--set language N'us_english'
Select Datepart(WeekDay,Getdate())
set Datefirst 1--更改本連線設定值:星期第一天為星期一(預設為7星期日)
Select Datepart(WeekDay,Getdate())

--4.DateAdd()/DateDiff()
--Returns a new datetime value
--保存期限二季
select getdate() as '製造日期',
DateAdd(Quarter,2,Getdate()) as '保存期限'
--
Select Getdate(),Datediff(hour, getdate(),'2009/1/1 12:00:00 AM')
--
Select EmployeeID,LastName,BirthDate,HireDate,
datediff(year,BirthDate,getdate()) as 'Age',
Convert(varchar,datediff(month,HireDate,getdate())/12)+'年'+
Convert(varchar,datediff(month,HireDate,getdate())%12)+'月' as '年資'
from dbo.Employees

參考網站:
http://technet.microsoft.com/zh-tw/library/ms187928.aspx