Sunny Books
What we have

SQL Server Cast and Convert

Both CAST and CONVERT convert an expression of one data type to another in SQL Server. CAST is ANSI standard and CONVERT is specific to SQL Server. CAST can't be used for formating purposes, but CONVERT can be used for formating purposes particularly on datetime and money datatype

Syntax for CAST

CAST ( expression AS data_type [ ( length ) ] )

Syntax for CONVERT

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Date and Time styles:

Style Date format
101mm/dd/yyyy
102yy.mm.dd
103dd/mm/yyyy
104dd.mm.yy
105dd-mm-yy

Examples

The following two examples get the same result.

SELECT name, birthday, CAST(birthday AS nvarchar) AS converted_birthday
FROM [em].[employee]

SELECT name, birthday , CONVERT(nvarchar,birthday) AS converted_birthday
FROM [em].[employee]

The result

Name Birthday converted_birthday
David1978-09-10 16:34:12.000Sep 10 1978 4:34PM
Alice1980-12-20 09:25:07.000Dec 20 1980 9:25AM
Rose1979-09-24 12:36:43.000Sep 24 1979 12:36PM
Jack1984-04-16 23:32:07.000Apr 16 1984 11:32PM
Tom1982-08-15 16:58:04.000Aug 15 1982 4:58PM
Alexa1986-11-09 09:10:28.000Nov 9 1986 9:10AM
Chantal1980-01-23 06:25:43.000Jan 23 1980 6:25AM
Ella1982-02-20 12:30:26.000Feb 20 1982 12:30PM
Kaven1978-05-25 16:08:27.000May 25 1978 4:08PM
Erik1982-06-11 10:32:50.000Jun 11 1982 10:32AM

Note: to control the formatting of the Date part, DateTime has to be converted to NVARCHAR using the styles provided. When converting to DATE data type, the CONVERT() function will ignore the style parameter.

Example1:

SELECT name, birthday , CAST(birthday AS nvarchar) AS nvarchar_birthday, 
	CAST(birthday AS DATE) AS date_birthday
FROM [em].[employee]

Result:

Name Birthday nvarchar_birthday date_birthday
David1978-09-10 16:34:12.000Sep 10 1978 4:34PM1978-09-10
Alice1980-12-20 09:25:07.000Dec 20 1980 9:25AM1980-12-20
Rose1979-09-24 12:36:43.000Sep 24 1979 12:36PM1979-09-24
Jack1984-04-16 23:32:07.000Apr 16 1984 11:32PM1984-04-16
Tom1982-08-15 16:58:04.000Aug 15 1982 4:58PM1982-08-15
Alexa1986-11-09 09:10:28.000Nov 9 1986 9:10AM1986-11-09
Chantal1980-01-23 06:25:43.000Jan 23 1980 6:25AM 1980-01-23
Ella1982-02-20 12:30:26.000Feb 20 1982 12:30PM1982-02-20
Kaven1978-05-25 16:08:27.000May 25 1978 4:08PM1978-05-25
Erik1982-06-11 10:32:50.000Jun 11 1982 10:32AM1982-06-11

Example2:

SELECT year(CONVERT(date, birthday)) as birth_year, count(employee_id) as people
FROM [em].[employee]
GROUP BY year(CONVERT(date, birthday))
year people
19782
19791
19802
19823
19841
19861

The difference between CAST and CONVERT:

  • CAST is based on ANSI standard and CONVERT is specific to SQL SERVER. If portability is a concern and if you want to use the sdcript with other database applications, use CAST().
  • CONVERT provides more flexibility than CAST. For example, it's possible to control how you want DateTime data types to be converted using styles with CONVERT function.

Note: the general guidline is to use CAST(), unless you want to take advantage of the style functionality in CONVERT().

SUNWEB EXPERT