It
converts an expression from one data
type to another.
CAST
and CONVERT have similar
functionality.
SQL
CAST and CONVERT
Syntax
Using CAST:
CAST (
expression AS data_type )
Using CONVERT:
CONVERT ( data_type [ ( length ) ] ,
expression [ , style ] )
Example
of SQL Cast and Convert
SQL Cast and Convert -
String
SELECT
SUBSTRING('CAST and CONVERT',
1, 3)
Return
Value =
CAS
(it get
from index 1 to 3)
SELECT
CAST('CAST
and CONVERT' AS char(3))
Return
Value =
CAS
(it get
3 char only)
SQL Cast and Convert - Date
Time
-Converting date time to character
data(vachar)
-The
default values (style 0 or 100, 9 or
109, 13 or 113, 20 or 120, and 21 or
121) always return the Without
century year(yy).
-Add
100 to a style value to get a
four-place year that includes the
century year(yyyy).
-Below
is example for converting 1 format
of date time to different format of
date time, so that it can be use in
various condition.
Value
of current Date Time GETDATE()
SELECT
(GETDATE()) = 2007-06-06
23:41:10.153
SELECT CONVERT(varchar,GETDATE(),0)
Return
Value =
Jun 6 2007 11:07PM
SELECT
CONVERT(varchar,GETDATE(),100)
Return
Value =
Jun 6 2007 11:07PM
SELECT
CONVERT(varchar,GETDATE(),1)
Return
Value =
06/06/07
SELECT
CONVERT(varchar,GETDATE(),101)
Return
Value =
06/06/2007
SELECT
CONVERT(varchar,GETDATE(),2)
Return
Value =
07.06.06
SELECT
CONVERT(varchar,GETDATE(),102)
Return
Value =
2007.06.06
SELECT
CONVERT(varchar,GETDATE(),3)
Return
Value =
06/06/07
SELECT
CONVERT(varchar,GETDATE(),103)
Return
Value =
06/06/2007
SELECT
CONVERT(varchar,GETDATE(),4)
Return
Value =
06.06.07
SELECT
CONVERT(varchar,GETDATE(),104)
Return
Value =
06.06.2007
SELECT
CONVERT(varchar,GETDATE(),5)
Return
Value =
06-06-07
SELECT
CONVERT(varchar,GETDATE(),105)
Return
Value =
06-06-2007
SELECT
CONVERT(varchar,GETDATE(),6)
Return
Value =
06 Jun 07
SELECT
CONVERT(varchar,GETDATE(),106)
Return
Value =
06 Jun 2007
SELECT
CONVERT(varchar,GETDATE(),7)
Return
Value =
Jun 06, 07
SELECT
CONVERT(varchar,GETDATE(),107)
Return
Value =
Jun 06, 2007
SELECT
CONVERT(varchar,GETDATE(),8)
Return
Value =
23:38:49
SELECT
CONVERT(varchar,GETDATE(),108)
Return
Value =
23:38:49
SELECT
CONVERT(varchar,GETDATE(),9)
Return
Value =
Jun 6 2007 11:39:17:060PM
SELECT
CONVERT(varchar,GETDATE(),109)
Return
Value =
Jun 6 2007 11:39:17:060PM
SELECT
CONVERT(varchar,GETDATE(),10)
Return
Value =
06-06-07
SELECT
CONVERT(varchar,GETDATE(),110)
Return
Value =
06-06-2007
SELECT
CONVERT(varchar,GETDATE(),11)
Return
Value =
07/06/06
SELECT
CONVERT(varchar,GETDATE(),111)
Return
Value =
2007/06/06
SELECT
CONVERT(varchar,GETDATE(),12)
Return
Value =
070606
SELECT
CONVERT(varchar,GETDATE(),112)
Return
Value =
20070606
SELECT
CONVERT(varchar,GETDATE(),13)
Return
Value =
06 Jun
2007 23:40:14:577
SELECT
CONVERT(varchar,GETDATE(),113)
Return
Value =
06 Jun 2007 23:40:14:577
SELECT
CONVERT(varchar,GETDATE(),14)
Return
Value =
23:40:29:717
SELECT
CONVERT(varchar,GETDATE(),114)
Return
Value =
23:40:29:717
SELECT
CONVERT(varchar,GETDATE(),20)
Return
Value =
2007-06-06 23:40:51
SELECT
CONVERT(varchar,GETDATE(),120)
Return
Value =
2007-06-06 23:40:51
SELECT
CONVERT(varchar,GETDATE(),21)
Return
Value =
2007-06-06 23:41:10.153
SELECT
CONVERT(varchar,GETDATE(),121)
Return
Value =
2007-06-06 23:41:10.153
SELECT
CONVERT(varchar,GETDATE(),126)
Return
Value =
2007-06-06T23:41:10.153
SELECT
CONVERT(varchar,GETDATE(),131)
Return
Value =
21/05/1428 11:41:10:153PM