學習筆記專區

select 2+'3' --5

 

--語法：cast(expression as 欲轉換之型態(寬度))

select 2+cast('3' as int)  --5

select cast(getdate() as varchar(20))  --03 22 2016 11:00AM

 

--convert(欲轉換之型態(寬度),expression,樣式)(樣式針對日期)

--101 mm/dd/yyyy

--102 yyyy.mm.dd

--121 yyyy-mm-dd-時間

select convert(varchar(20),getdate())--03 22 2016 11:00AM

select convert(varchar(20),getdate(),101)--03/22/2016

select convert(varchar(20),getdate(),102)--2016.03.22

select convert(varchar(20),getdate(),121)--2016-03-22 11:03:00

 

--convert應用：

select last_name,CONVERT(Varchar(20),hire_date,101)hireDATE

from EMPLOYEES

last_name	hireDATE
King	06/17/1987
Kochhar	09/21/1989
De Haan	01/13/1993
Hunold	01/03/1990
Ernst	05/21/1991
Lorentz	02/07/1999
Mourgos	11/16/1999
Rajs	10/17/1995
Davies	01/29/1997
Matos	03/15/1998
Vargas	07/09/1998
Zlotkey	01/29/2000
Abel	05/11/1996
Taylor	03/24/1998
Grant	05/24/1999
Whalen	09/17/1987
Hartstein	02/17/1996
Fay	08/17/1997
Higgins	06/07/1994
Gietz	06/07/1994

---

---

ISNULL函數

ISNULL(欄位，欲替換空值之值)

select last_name,commission_pct

from EMPLOYEES

 

last_name	commission_pct
Hunold	NULL
Ernst	NULL
Lorentz	NULL
Mourgos	NULL
Rajs	NULL
Davies	NULL
Matos	NULL
Vargas	NULL
Zlotkey	0.2
Abel	0.3
Taylor	0.2
Grant	0.15
Whalen	NULL
Hartstein	NULL
Fay	NULL
Higgins	NULL
Gietz	NULL

 

select last_name,isnull(commission_pct,0)--注意改變的值不可以跟欄位設定的值不一樣，例如commission_pct欄位設定為放數字，則不可以設定成這樣：isnull(commission_pct,'不是數字')

from EMPLOYEES

last_name	commission_pct
King	0
Kochhar	0
De Haan	0
Hunold	0
Ernst	0
Lorentz	0
Mourgos	0
Rajs	0
Davies	0
Matos	0
Vargas	0
Zlotkey	0.2
Abel	0.3
Taylor	0.2
Grant	0.15
Whalen	0
Hartstein	0
Fay	0
Higgins	0
Gietz	0