SQL Server Functions that helps to convert date and time values to and from string literals and other date and time formats.

By Shemeer NS, 5 May 2013

Download Scripts.zip - 4.3 KB

Introduction
Which are those functions?
More Info

CAST

Definition
Samples

CONVERT

Definition
Converting DATETIME to VARCHAR
Converting VARCHAR to DATETIME
Convert Styles
Samples

FORMAT

Definition
Standard Date and Time Format Strings
Custom Date and Time Format Strings
Samples

PARSE

Definition
Samples

TRY_PARSE

Definition
Samples

TRY_CONVERT

Definition
Samples

References
Summary

Introduction

When we work with any programming languages we often required to perform data type casting and converting. Conversion can be from one of the following options

one data type to another,
from one format to another format,
from one culture to another culture.

When talking about date and time, different languages uses different standards (format) and the developer needs to take care about the date and or time format as per the geographic locations (or languages). This article will go through all the SQL Server Functions that helps to convert date and time values to and from string literals and other date and time formats. The function names are given below,

Function Name	2000	2005	2008	2008 R2	2012
CAST()	Y	Y	Y	Y	Y
CONVERT()	Y	Y	Y	Y	Y
FORMAT()	N	N	N	N	Y
PARSE()	N	N	N	N	Y
TRY_PARSE()	N	N	N	N	Y
TRY_CONVERT()	N	N	N	N	Y

All the above functions except FORMAT() are Conversion Functions of Transact-SQL whereas FORMAT() is a Transact-SQL String Function. We will go through all the above functions with detailed samples. You can find all the SQL queries used here as a .zip attachment with this article.

I hope you will enjoy this article. I have put my time and efforts on all of my articles, Please don't forget to mark your votes, suggestions and feedback to improve the quality of this and upcoming articles.

Note: All the above mentioned functions will be explained only from date and time perspective.

CAST()

Explicitly converts an expression of one data type to another.CAST and CONVERT provide similar functionality.

Syntax :-

CAST ( expression AS data_type [ (length)])

Here 'expression' can be any valid expression, 'data_type' is the target data type and 'length' is an optional integer that specifies the length of the target data type. The default value is 30.

SELECT CAST('SQL Server Functions that helps to convert date and time values to and from string literals and other date and time formats' AS VARCHAR) AS TITLE

If you see the above sql query , I have not specified the length. so the length will be considered as 30 the default length for CAST , CONVERT.

TITLE
------------------------------
SQL Server Functions that help

The output got truncated...The above SQL statement was just to demonstrate the use of length.

Let's see how we can cast a string date or string time to datetime data type.

SELECT CAST('20130416' AS datetime) AS 'DateValue'
SELECT CAST('2013-04-16' AS datetime) AS 'DateValue'
SELECT CAST('11:20:25' AS datetime) AS 'TimeValue'

The above query will demonstrate the casting of string to a datetime data type. The output generated are given below,

DateValue
-----------------------
2013-04-16 00:00:00.000
 
DateValue
-----------------------
2013-04-16 00:00:00.000
 
TimeValue
-----------------------
1900-01-01 11:20:25.000

CONVERT()

Explicitly converts an expression of one data type to another.CASTandCONVERTprovide similar functionality.

Syntax :-

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

Here 'expression' can be any validexpression, 'data_type' is the target data type and 'length' is an optional integer that specifies the length of the target data type. The default value is 30. Style is an integer expression that specifies how the CONVERT function is to translate expression. If style is NULL then NULL is returned. The range is determined by data_type.

Now we will explore the different styles of date and time formatting with CONVERT.

Converting DATETIME to VARCHAR

The Transact-SQL (T-SQL) Convert function can be used to convert data between different types. When converting a DATETIME value to a VARCHAR value a style code can be used for getting the output in different format.

SELECT CONVERT(VARCHAR, GETDATE(), 0) -- May  4 2013  2:14PM

Converting VARCHAR to DATETIME

The style code is equally important when converting a VARCHAR to a DATETIME value. I'm using the output from the previous sql code and different style codes, lets see how it works.

SELECT CONVERT(DATETIME, 'May  4 2013  2:14PM', 0) -- 2013-05-04 14:14:00.000
SELECT CONVERT(DATETIME, 'May  4 2013  2:14PM', 130) -- Conversion failed when converting date and/or time from character string.

Date and Time Styles

When working with datetime, style can be one of the values shown in the following table. The example column shows conversion from datetime to varchar and vice versa.

Date Format	Code	Standard and Examples
mon dd yyyy hh:miAM (or PM)	0 or 100	Default. Equivalent to not specifying a style code. SELECT CONVERT(VARCHAR, GETDATE(), 0) -- May 4 2013 2:14PM SELECT CONVERT(VARCHAR, GETDATE(), 100) -- May 4 2013 2:14PM SELECT CONVERT(DATETIME, 'May 4 2013 2:14PM', 0) -- 2013-05-04 14:14:00.000 SELECT CONVERT(DATETIME, 'May 4 2013 2:14PM', 100) -- 2013-05-04 14:14:00.000
mm/dd/yy	1	U.S. SELECT CONVERT(VARCHAR, GETDATE(), 1) -- 05/04/13 SELECT CONVERT(DATETIME, '05/04/13', 1) -- 2013-05-04 00:00:00.000
mm/dd/yyyy	101	U.S. SELECT CONVERT(VARCHAR, GETDATE(), 101) -- 05/04/2013 SELECT CONVERT(DATETIME, '05/04/2013', 101) -- 2013-05-04 00:00:00.000
yy.mm.dd	2	ANSI. SELECT CONVERT(VARCHAR, GETDATE(), 2) -- 13.05.04 SELECT CONVERT(DATETIME, '13.05.04', 2) -- 2013-05-04 00:00:00.000
yyyy.mm.dd	102	ANSI. SELECT CONVERT(VARCHAR, GETDATE(), 102) -- 2013.05.04 SELECT CONVERT(DATETIME, '2013.05.04', 102) -- 2013-05-04 00:00:00.000
dd/mm/yy	3	British/French. SELECT CONVERT(VARCHAR, GETDATE(), 3) -- 04/05/13 SELECT CONVERT(DATETIME, '04/05/13', 3) -- 2013-05-04 00:00:00.000
dd/mm/yyyy	103	British/French. SELECT CONVERT(VARCHAR, GETDATE(), 103) -- 04/05/2013 SELECT CONVERT(DATETIME, '04/05/2013', 103) -- 2013-05-04 00:00:00.000
dd.mm.yy	4	German. SELECT CONVERT(VARCHAR, GETDATE(), 4) -- 04.05.13 SELECT CONVERT(DATETIME, '04.05.13', 4) -- 2013-05-04 00:00:00.000
dd.mm.yyyy	104	German. SELECT CONVERT(VARCHAR, GETDATE(), 104) -- 04.05.2013 SELECT CONVERT(DATETIME, '04.05.2013', 104) -- 2013-05-04 00:00:00.000
dd-mm-yy	5	Italian. SELECT CONVERT(VARCHAR, GETDATE(), 5) -- 04-05-13 SELECT CONVERT(DATETIME, '04-05-13', 5) -- 2013-05-04 00:00:00.000
dd-mm-yyyy	105	Italian. SELECT CONVERT(VARCHAR, GETDATE(), 105) -- 04-05-2013 SELECT CONVERT(DATETIME, '04-05-2013', 105) -- 2013-05-04 00:00:00.000
dd mon yy	6	SELECT CONVERT(VARCHAR, GETDATE(), 6) -- 04 May 13 SELECT CONVERT(DATETIME, '04 May 13', 6) -- 2013-05-04 00:00:00.000
dd mon yyyy	106	SELECT CONVERT(VARCHAR, GETDATE(), 106) -- 04 May 2013 SELECT CONVERT(DATETIME, '04 May 2013', 106) -- 2013-05-04 00:00:00.000
Mon dd, yy	7	SELECT CONVERT(VARCHAR, GETDATE(), 7) -- May 04, 13 SELECT CONVERT(DATETIME, 'May 04, 13', 7) -- 2013-05-04 00:00:00.000
Mon dd, yyyy	107	SELECT CONVERT(VARCHAR, GETDATE(), 107) -- May 04, 2013 SELECT CONVERT(DATETIME, 'May 04, 2013', 107) -- 2013-05-04 00:00:00.000
hh:mi:ss	8	SELECT CONVERT(VARCHAR, GETDATE(), 8) -- 14:14:35 SELECT CONVERT(DATETIME, '14:14:35', 8) -- 1900-01-01 14:14:35.000 sdfsdf
hh:mi:ss	108	SELECT CONVERT(VARCHAR, GETDATE(), 108) -- 14:14:35 SELECT CONVERT(DATETIME, '14:14:35', 108) -- 1900-01-01 14:14:35.000
mon dd yyyy hh:mi:ss:mmmAM (or PM)	9 or 109	Default + milliseconds. SELECT CONVERT(VARCHAR, GETDATE(), 9) -- May 4 2013 2:14:35:020PM SELECT CONVERT(DATETIME, 'May 4 2013 2:14:35:020PM', 9) -- 2013-05-04 14:14:35.020 SELECT CONVERT(VARCHAR, GETDATE(), 109) -- May 4 2013 2:14:35:020PM SELECT CONVERT(DATETIME, 'May 4 2013 2:14:35:020PM', 109) -- 2013-05-04 14:14:35.020
mm-dd-yy	10	USA. SELECT CONVERT(VARCHAR, GETDATE(), 10) -- 05-04-13 SELECT CONVERT(DATETIME, '05-04-13', 10) -- 2013-05-04 00:00:00.000
mm-dd-yyyy	110	USA. SELECT CONVERT(VARCHAR, GETDATE(), 110) -- 05-04-2013 SELECT CONVERT(DATETIME, '05-04-2013', 110) -- 2013-05-04 00:00:00.000
yy/mm/dd	11	JAPAN. SELECT CONVERT(VARCHAR, GETDATE(), 11) -- 13/05/04 SELECT CONVERT(DATETIME, '13/05/04', 11) -- 2013-05-04 00:00:00.000
yyyy/mm/dd	111	JAPAN. SELECT CONVERT(VARCHAR, GETDATE(), 111) -- 2013/05/04 SELECT CONVERT(DATETIME, '2013/05/04', 111) -- 2013-05-04 00:00:00.000
yymmdd	12	ISO. SELECT CONVERT(VARCHAR, GETDATE(), 12) -- 130504 SELECT CONVERT(DATETIME, '130504', 12) -- 2013-05-04 00:00:00.000
yyyymmdd	112	ISO. SELECT CONVERT(VARCHAR, GETDATE(), 112) -- 20130504 SELECT CONVERT(DATETIME, '20130504', 112) -- 2013-05-04 00:00:00.000
dd mon yyyy hh:mi:ss:mmm(24h)	13 or 113	Europe default + milliseconds. SELECT CONVERT(VARCHAR, GETDATE(), 13) -- 04 May 2013 14:14:35:020 SELECT CONVERT(DATETIME, '04 May 2013 14:14:35:020', 13) -- 2013-05-04 14:14:35.020 SELECT CONVERT(VARCHAR, GETDATE(), 113) -- 04 May 2013 14:14:35:020 SELECT CONVERT(DATETIME, '04 May 2013 14:14:35:020', 113) -- 2013-05-04 14:14:35.020
hh:mi:ss:mmm(24h)	14	SELECT CONVERT(VARCHAR, GETDATE(), 14) -- 14:14:35:020 SELECT CONVERT(DATETIME, '14:14:35:020', 14) -- 1900-01-01 14:14:35.020
hh:mi:ss:mmm(24h)	114	SELECT CONVERT(VARCHAR, GETDATE(), 114) -- 14:14:35:020 SELECT CONVERT(DATETIME, '14:14:35:020', 114) -- 1900-01-01 14:14:35.020
yyyy-mm-dd hh:mi:ss(24h)	20 or 120	ODBC canonical. SELECT CONVERT(VARCHAR, GETDATE(), 20) -- 2013-05-04 14:14:35 SELECT CONVERT(DATETIME, '2013-05-04 14:14:35', 20) -- 2013-05-04 14:14:35.000 SELECT CONVERT(VARCHAR, GETDATE(), 120) -- 2013-05-04 14:14:35 SELECT CONVERT(DATETIME, '2013-05-04 14:14:35', 120) -- 2013-05-04 14:14:35.000
yyyy-mm-dd hh:mi:ss.mmm(24h)	21 or 121	ODBC canonical (with milliseconds). SELECT CONVERT(VARCHAR, GETDATE(), 21) -- 2013-05-04 14:14:35.073 SELECT CONVERT(DATETIME, '2013-05-04 14:14:35.073', 21) -- 2013-05-04 14:14:35.073 SELECT CONVERT(VARCHAR, GETDATE(), 121) -- 2013-05-04 14:14:35.073 SELECT CONVERT(DATETIME, '2013-05-04 14:14:35.073', 121) -- 2013-05-04 14:14:35.073
yyyy-mm-ddThh:mi:ss.mmm (no spaces)	126	ISO8601. SELECT CONVERT(VARCHAR, GETDATE(), 126) -- 2013-05-04T14:14:35.073 SELECT CONVERT(DATETIME, '2013-05-04T14:14:35.073', 126) -- 2013-05-04 14:14:35.073
yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)	127	ISO8601 with time zone Z. Designed for XML use. SELECT CONVERT(VARCHAR, GETDATE(), 127) -- 2013-05-04T14:14:35.073 SELECT CONVERT(DATETIME, '2013-05-04T14:14:35.073', 127) -- 2013-05-04 14:14:35.073
dd mon yyyy hh:mi:ss:mmmAM	130	Hijri. SELECT CONVERT(NVARCHAR, GETDATE(), 130) -- 24 جمادى الثانية 1434 2:14:35 SELECT CONVERT(DATETIME, '24 جمادى الثانية 1434 2:14:35', 130) -- 2013-05-04 14:14:35.073
dd/mm/yy hh:mi:ss:mmmAM	131	Hijri. SELECT CONVERT(VARCHAR, GETDATE(), 131) -- 24/06/1434 2:14:35:073PM SELECT CONVERT(DATETIME, '24/06/1434 2:14:35:073PM', 131) -- 2013-05-04 14:14:35.073

The Date Format strings are just for representational purpose and cannot be used directly in FORMAT() function's format parameter. The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy). Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.

FORMAT()

Returns a value formatted with the specified format and optional culture. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. FORMAT returns NULL for errors other than a culture that is not valid. For example, NULL is returned if the value specified in format is not valid. FORMAT relies on the presence of .the .NET Framework Common Language Runtime (CLR). Click here to read more from msdn.

Syntax :-

FORMAT ( value, format [, culture ] )

Where value is an expression of a supported data type to format. The format argument must contain a valid .NET Framework format string, either as a standard format string or as a pattern of custom characters for dates and numeric values. Composite formatting is not supported. format argument is in an nvarchar format pattern.

From below tables we can learn standard and custom date and time formatting options with FORMAT funtion.

Standard Date and Time Format Strings

A standard date and time format string uses a single format specifier to define the text representation of a date and time value. Below given table shows all standard date and time format strings, if you use any single character as format string then it will consider it as a single format specifier (standard), if it's an invalid single format specifier then NULL will be retuned.

In a formatting operation, a standard format string is simply an alias for a custom format string. The advantage of using an alias to refer to a custom format string is that, although the alias remains invariant, the custom format string itself can vary. This is important because the string representations of date and time values typically vary by culture. For example, the "d" standard format string indicates that a date and time value is to be displayed using a short date pattern. For the invariant culture, this pattern is "MM/dd/yyyy". For the fr-FR culture, it is "dd/MM/yyyy". For the ja-JP culture, it is "yyyy/MM/dd".

Single Format Specifier

Description and Examples

'd'

Short date pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'd') --4/28/2013
SELECT FORMAT(@d, 'd', 'en-US') --4/28/2013
SELECT FORMAT(@d, 'd', 'fr-FR') --28/04/2013
SELECT FORMAT(@d, 'd', 'ja-JP'); --2013/04/28

'D'

Long date pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'D') --Sunday, April 28, 2013
SELECT FORMAT(@d, 'D', 'sv-SE') --den 28 april 2013
SELECT FORMAT(@d, 'D', 'fr-FR') --dimanche 28 avril 2013
SELECT FORMAT(@d, 'D', 'ja-JP'); --2013年4月28日

'f'

Full date/time pattern (short time).

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'f') --Sunday, April 28, 2013 4:54 PM
SELECT FORMAT(@d, 'f', 'de-DE') --Sonntag, 28. April 2013 16:54
SELECT FORMAT(@d, 'f', 'fr-FR') --dimanche 28 avril 2013 16:54
SELECT FORMAT(@d, 'f', 'sv-SE'); --den 28 april 2013 16:54

'g'

Full date/time pattern (long time).

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'g') --4/28/2013 4:54 PM
SELECT FORMAT(@d, 'g', 'de-DE') --28.04.2013 16:54
SELECT FORMAT(@d, 'g', 'fr-FR') --28/04/2013 16:54
SELECT FORMAT(@d, 'g', 'sv-SE'); --2013-04-28 16:54

'G'

General date/time pattern (short time).

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'G') --4/28/2013 4:54:08 PM
SELECT FORMAT(@d, 'G', 'hr-HR') --28.4.2013. 16:54:08
SELECT FORMAT(@d, 'G', 'id-ID') --28/04/2013 16:54:08
SELECT FORMAT(@d, 'G', 'el-GR'); --28/4/2013 4:54:08 μμ

'M', 'm'

Month/day pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'M') --April 28
SELECT FORMAT(@d, 'M', 'hr-HR') --28. travnja
SELECT FORMAT(@d, 'M', 'id-ID') --28 April
SELECT FORMAT(@d, 'M', 'el-GR'); --28 Απριλίου
SELECT FORMAT(@d, 'm') --April 28
SELECT FORMAT(@d, 'm', 'hr-HR') --28. travnja
SELECT FORMAT(@d, 'm', 'id-ID') --28 April
SELECT FORMAT(@d, 'm', 'el-GR'); --28 Απριλίου

'O', 'o'

Round-trip date/time pattern. The pattern for this specifier reflects a defined standard (ISO 8601). Therefore, it is always the same regardless of the culture used or the format provider supplied.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'O') --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'O', 'hr-HR') --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'O', 'id-ID') --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'O', 'el-GR'); --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'o') --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'o', 'hr-HR') --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'o', 'id-ID') --2013-04-28T16:54:08.0000000
SELECT FORMAT(@d, 'o', 'el-GR'); --2013-04-28T16:54:08.0000000

'R', 'r'

RFC1123 pattern.The output will be same regardless of the culture used or the format provider supplied.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'R') --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'R', 'hr-HR') --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'R', 'id-ID') --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'R', 'el-GR'); --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'r') --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'r', 'hr-HR') --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'r', 'id-ID') --Sun, 28 Apr 2013 16:54:08 GMT
SELECT FORMAT(@d, 'r', 'el-GR'); --Sun, 28 Apr 2013 16:54:08 GMT

's'

Sortable date/time pattern. The pattern reflects a defined standard (ISO 8601), and the property is read-only. Therefore, it is always the same, regardless of the culture used or the format provider supplied.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 's') --2013-04-28T16:54:08
SELECT FORMAT(@d, 's', 'hr-HR') --2013-04-28T16:54:08
SELECT FORMAT(@d, 's', 'id-ID') --2013-04-28T16:54:08
SELECT FORMAT(@d, 's', 'el-GR'); --2013-04-28T16:54:08

't'

Short time pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 't') --4:54 PM
SELECT FORMAT(@d, 't', 'hr-HR') --16:54
SELECT FORMAT(@d, 't', 'ar-EG') --04:54 م
SELECT FORMAT(@d, 't', 'el-GR'); --4:54 μμ

'T'

Long time pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'T') --4:54:08 PM
SELECT FORMAT(@d, 'T', 'hr-HR') --16:54:08
SELECT FORMAT(@d, 'T', 'ar-EG') --04:54:08 م
SELECT FORMAT(@d, 'T', 'el-GR'); --4:54:08 μμ

'u'

Universal sortable date/time pattern. The output will be same regardless of the culture used or the format provider supplied.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'u') --2013-04-28 16:54:08Z
SELECT FORMAT(@d, 'u', 'hr-HR') --2013-04-28 16:54:08Z
SELECT FORMAT(@d, 'u', 'ar-EG') --2013-04-28 16:54:08Z
SELECT FORMAT(@d, 'u', 'el-GR'); --2013-04-28 16:54:08Z

'U'

Universal full date/time pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'U') --Sunday, April 28, 2013 11:24:08 AM
SELECT FORMAT(@d, 'U', 'hr-HR') --28. travnja 2013. 11:24:08
SELECT FORMAT(@d, 'U', 'SV-se') --den 28 april 2013 11:24:08
SELECT FORMAT(@d, 'U', 'el-GR'); --Κυριακή, 28 Απριλίου 2013 11:24:08 πμ

'Y', 'y'

Year month pattern.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'Y') --April, 2013
SELECT FORMAT(@d, 'Y', 'hr-HR') --travanj, 2013
SELECT FORMAT(@d, 'Y', 'id-ID') --April, 2013
SELECT FORMAT(@d, 'Y', 'el-GR'); --Απρίλιος 2013
SELECT FORMAT(@d, 'y') --April, 2013
SELECT FORMAT(@d, 'y', 'hr-HR') --travanj, 2013
SELECT FORMAT(@d, 'y', 'id-ID') --April, 2013
SELECT FORMAT(@d, 'y', 'el-GR'); --Απρίλιος 2013

Any other single character

Unknown specifier. Returns NULL.

DECLARE @d DATETIME
SET @d='4/28/2013 4:54:08 PM'
 
SELECT FORMAT(@d, 'Q') --NULL

Remarks: The standard format string (Single Format Specifier) serves as a convenient abbreviation for a longer custom format string that is invariant. Four standard format strings fall into this category: "O" (or "o"), "R" (or "r"), "s", and "u". These strings correspond to custom format strings defined by the invariant culture. They produce string representations of date and time values that are intended to be identical across cultures.

Single Format Specifier	Equivalent Custom Format String
'O', 'o	'yyyy-MM-ddTHH:mm:ss.fffffff'
'R', 'r'	'ddd, dd MMM yyyy HH:mm:ss "GMT"'
's'	'yyyy-MM-ddTHH:mm:ss'
'u'	'yyyy-MM-dd HH:mm:ss"Z"'

Note : A single format specifier used as standard date and time format string to define the text representation of a date and time value. Any date and time format string that contains more than one character, including white space, is interpreted as a custom date and time format string;

All single format specifier is easy to use if incase we want more customized format then we can go for cutom date and time format string.

Custom Date and Time Format Strings

A custom format string consists of one or more custom date and time format specifiers. Any string that is not a standard date and time format string is interpreted as a custom date and time format string.

Custom Format Specifier	Description and Examples
	Note : To use any of the custom date and time format specifiers as the only specifier in a format string (that is, to use the "d", "f", "F", "g", "h", "H", "K", "m", "M", "s", "t", "y", "z", ":", or "/" custom format specifier by itself), include a space before or after the specifier, or include a percent ("%") format specifier before the single custom date and time specifier.
--Day Format
'd'	The day of the month, from 1 through 31. DECLARE @d DATETIME SET @d='4/9/2013 4:54:08 PM' SELECT FORMAT(@d, 'd ')--9 SELECT FORMAT(@d, 'd-M-yyyy')--9-4-2013 SELECT FORMAT(@d, 'd MMMM')--9 April SELECT FORMAT(@d, 'd MMMM', 'fr-FR')--9 avril
'dd'	The day of the month, from 01 through 31. DECLARE @d DATETIME SET @d='4/9/2013 4:54:08 PM' SELECT FORMAT(@d, 'dd')--09 SELECT FORMAT(@d, 'dd-M-yyyy')--09-4-2013 SELECT FORMAT(@d, 'dd MMMM')--09 April SELECT FORMAT(@d, 'dd MMMM', 'fr-FR')--09 avril
'ddd'	The abbreviated name of the day of the week. DECLARE @d DATETIME SET @d='4/9/2013 4:54:08 PM' SELECT FORMAT(@d, 'ddd d')--Tue 9 SELECT FORMAT(@d, 'ddd dd-M-yyyy')--Tue 09-4-2013 SELECT FORMAT(@d, 'ddd dd MMMM')--Tue 09 April SELECT FORMAT(@d, 'ddd dd MMMM', 'fr-FR')--mar. 09 avril
'dddd'	The full name of the day of the week. DECLARE @d DATETIME SET @d='4/9/2013 4:54:08 PM' SELECT FORMAT(@d, 'dddd d')--Tuesday 9 SELECT FORMAT(@d, 'dddd dd-M-yyyy')--Tuesday 09-4-2013 SELECT FORMAT(@d, 'dddd dd MMMM')--Tuesday 09 April SELECT FORMAT(@d, 'dddd dd MMMM', 'fr-FR')--mardi 09 avril
--Month Format
'M'	The month, from 1 through 12. DECLARE @d DATETIME SET @d='4/9/2013 4:54:08 PM' SELECT FORMAT(@d, 'M ')--4 SELECT FORMAT(@d, 'dd-M-yyyy', 'fr-FR')--09-4-2013
'MM'	The month, from 01 through 12. DECLARE @d DATETIME SET @d='4/9/2013 4:54:08 PM' SELECT FORMAT(@d, 'MM')--04 SELECT FORMAT(@d, 'dd-MM-yyyy', 'fr-FR')--09-04-2013
'MMM'	The abbreviated name of the month. DECLARE @d DATETIME SET @d='4/9/2013 4:54:08 PM' SELECT FORMAT(@d, 'MMM')--Apr SELECT FORMAT(@d, 'MMM', 'ml-IN')--ഏപ്റില് SELECT FORMAT(@d, 'MMM', 'fr-FR')--avr.
'MMMM'	The full name of the month. DECLARE @d DATETIME SET @d='4/9/2013 4:54:08 PM' SELECT FORMAT(@d, 'MMMM')--April SELECT FORMAT(@d, 'MMMM', 'ml-IN')--ഏപ്റില് SELECT FORMAT(@d, 'MMMM', 'fr-FR')--avril
--Year Format
'y'	The year, from 0 to 99. DECLARE @d DATETIME SET @d='4/9/2001 4:54:08 PM' SELECT FORMAT(@d, 'y ')--1 SELECT FORMAT(@d, 'd-M-y')--9-4-1 SELECT FORMAT(@d, 'd MMMM y')--9 April 1 SELECT FORMAT(@d, 'd MMMM y', 'fr-FR')--9 avril 1
'yy'	The year, from 00 to 99. DECLARE @d DATETIME SET @d='4/9/2001 4:54:08 PM' SELECT FORMAT(@d, 'yy')--01 SELECT FORMAT(@d, 'd-M-yy')--9-4-01 SELECT FORMAT(@d, 'd MMMM yy')--9 April 01 SELECT FORMAT(@d, 'd MMMM yy', 'fr-FR')--9 avril 01
'yyy'	The year, with a minimum of three digits. DECLARE @d DATETIME SET @d='4/9/2001 4:54:08 PM' SELECT FORMAT(@d, 'yyy')--2001 SELECT FORMAT(@d, 'd-M-yyy')--9-4-2001 SELECT FORMAT(@d, 'd MMMM yyy')--9 April 2001 SELECT FORMAT(@d, 'd MMMM yyy', 'fr-FR')--9 avril 2001
'yyyy'	The year as a four-digit number. DECLARE @d DATETIME SET @d='4/9/2001 4:54:08 PM' SELECT FORMAT(@d, 'yyyy')--2001 SELECT FORMAT(@d, 'd-M-yyyy')--9-4-2001 SELECT FORMAT(@d, 'd MMMM yyyy')--9 April 2001 SELECT FORMAT(@d, 'd MMMM yyyy', 'fr-FR')--9 avril 2001
'yyyyy'	The year as a five-digit number. DECLARE @d DATETIME SET @d='4/9/2001 4:54:08 PM' SELECT FORMAT(@d, 'yyyyy')--02001 SELECT FORMAT(@d, 'd-M-yyyyy')--9-4-02001 SELECT FORMAT(@d, 'd MMMM yyyyy')--9 April 02001 SELECT FORMAT(@d, 'd MMMM yyyyy', 'fr-FR')--9 avril 02001
--Era Format
'g', 'gg'	The period or era. DECLARE @d DATETIME SET @d='4/9/2001 4:54:08 PM' SELECT FORMAT(@d, 'MM/dd/yyyy g')--04/09/2001 A.D. SELECT FORMAT(@d, 'MM/dd/yyyy g', 'fr-FR')--04/09/2001 ap. J.-C. SELECT FORMAT(@d, 'MM/dd/yyyy gg')--04/09/2001 A.D. SELECT FORMAT(@d, 'MM/dd/yyyy gg', 'fr-FR')--04/09/2001 ap. J.-C.
--Hour (Time Format)
'h'	The hour, using a 12-hour clock from 1 to 12. DECLARE @d DATETIME SET @d='5/9/2001 4:54:08 PM' SELECT FORMAT(@d, 'h ')--4 SELECT FORMAT(@d, 'h:m:s')--4:54:8 SELECT FORMAT(@d, 'h:m:s', 'fr-FR')--4:54:8
'hh'	The hour, using a 12-hour clock from 01 to 12. DECLARE @d DATETIME SET @d='5/9/2001 4:54:08 PM' SELECT FORMAT(@d, 'hh')--04 SELECT FORMAT(@d, 'hh:m:s')--04:54:8 SELECT FORMAT(@d, 'hh:m:s', 'fr-FR')--04:54:8
'H'	The hour, using a 24-hour clock from 0 to 23. DECLARE @d DATETIME SET @d='5/9/2001 6:54:08 AM' SELECT FORMAT(@d, 'H ')--6 SELECT FORMAT(@d, 'H:m:s')--6:54:8 SELECT FORMAT(@d, 'H:m:s', 'fr-FR')--6:54:8
'HH'	The hour, using a 24-hour clock from 00 to 23. DECLARE @d DATETIME SET @d='5/9/2001 6:54:08 AM' SELECT FORMAT(@d, 'HH')--06 SELECT FORMAT(@d, 'HH:m:s')--06:54:8 SELECT FORMAT(@d, 'HH:m:s', 'fr-FR')--06:54:8
--Min (Time Format)
'm'	The minute, from 0 through 59. DECLARE @d DATETIME SET @d='5/9/2001 6:09:08 AM' SELECT FORMAT(@d, 'm ')--9 SELECT FORMAT(@d, 'mm')--09
'mm'	The minute, from 00 through 59. DECLARE @d DATETIME SET @d='5/9/2001 6:09:08 AM' SELECT FORMAT(@d, 'm ')--9 SELECT FORMAT(@d, 'mm')--09
--Sec (Time Format)
's'	The second, from 0 through 59. DECLARE @d DATETIME SET @d='5/9/2001 6:09:08 AM' SELECT FORMAT(@d, 's ')--8 SELECT FORMAT(@d, 'ss')--08
'ss'	The second, from 00 through 59. DECLARE @d DATETIME SET @d='5/9/2001 6:09:08 AM' SELECT FORMAT(@d, 's ')--8 SELECT FORMAT(@d, 'ss')--08
--Sec Fraction (Time Format)
'f'	The tenths of a second in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.123456789 AM' SELECT FORMAT(@d, 'hh:mm:ss:f')--06:09:59:1
'ff'	The hundredths of a second in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.123456789 AM' SELECT FORMAT(@d, 'hh:mm:ss:ff')--06:09:59:12
'fff'	The milliseconds in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.123456789 AM' SELECT FORMAT(@d, 'hh:mm:ss:fff')--06:09:59:123
'ffff'	The ten thousandths of a second in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.123456789 AM' SELECT FORMAT(@d, 'hh:mm:ss:ffff')--06:09:59:1234
'fffff	The hundred thousandths of a second in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.123456789 AM' SELECT FORMAT(@d, 'hh:mm:ss:fffff')--06:09:59:12345
'ffffff'	The millionths of a second in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.123456789 AM' SELECT FORMAT(@d, 'hh:mm:ss:ffffff')--06:09:59:123456
'fffffff'	The ten millionths of a second in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.123456789 AM' SELECT FORMAT(@d, 'hh:mm:ss:fffffff')--06:09:59:1234568
'F'	If non-zero, the tenths of a second in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.012345009 AM' SELECT FORMAT(@d, 'hh:mm:ss f')--06:09:59 0 SELECT FORMAT(@d, 'hh:mm:ss F')--06:09:59
'FF'	If non-zero, the hundredths of a second in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.012345009 AM' SELECT FORMAT(@d, 'hh:mm:ss ff')--06:09:59 01 SELECT FORMAT(@d, 'hh:mm:ss FF')--06:09:59 01
'FFF	If non-zero, the milliseconds in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.012345009 AM' SELECT FORMAT(@d, 'hh:mm:ss fff')--06:09:59 012 SELECT FORMAT(@d, 'hh:mm:ss FFF')--06:09:59 012
'FFFF'	If non-zero, the ten thousandths of a second in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.012345009 AM' SELECT FORMAT(@d, 'hh:mm:ss ffff')--06:09:59 0123 SELECT FORMAT(@d, 'hh:mm:ss FFFF')--06:09:59 0123
'FFFFF'	If non-zero, the hundred thousandths of a second in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.012345009 AM' SELECT FORMAT(@d, 'hh:mm:ss fffff')--06:09:59 01234 SELECT FORMAT(@d, 'hh:mm:ss FFFFF')--06:09:59 01234
'FFFFFF'	If non-zero, the millionths of a second in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.012345009 AM' SELECT FORMAT(@d, 'hh:mm:ss ffffff')--06:09:59 012345 SELECT FORMAT(@d, 'hh:mm:ss FFFFFF')--06:09:59 012345
'FFFFFFF'	If non-zero, the ten millionths of a second in a date and time value. DECLARE @d DATETIME2 SET @d='5/9/2001 6:09:59.012345009 AM' SELECT FORMAT(@d, 'hh:mm:ss fffffff')--06:09:59 0123450 SELECT FORMAT(@d, 'hh:mm:ss FFFFFFF')--06:09:59 012345
--Time Zone (Time Format)
'K'	Time zone information. DECLARE @d DATETIMEOFFSET SET @d='6/15/2009 1:45:30 AM -07:00' SELECT FORMAT(@d, 'K ')-- -07:00
--AM/PM designator (Time Format)
't'	The first character of the AM/PM designator. DECLARE @d DATETIME SET @d='5/9/2001 6:09:59 AM' SELECT FORMAT(@d, 'hh:mm:ss t')--06:09:59 A
'tt'	The AM/PM designator. DECLARE @d DATETIME SET @d='5/9/2001 6:09:59 AM' SELECT FORMAT(@d, 'hh:mm:ss tt')--06:09:59 AM
--Offset, UTC (Time Format)
'z'	Hours offset from UTC, with no leading zeros. DECLARE @d DATETIMEOFFSET SET @d='6/15/2009 1:45:30 AM +05:30' SELECT FORMAT(@d, 'z ') -- +5
'zz'	Hours offset from UTC, with a leading zero for a single-digit value. DECLARE @d DATETIMEOFFSET SET @d='6/15/2009 1:45:30 AM +5:30' SELECT FORMAT(@d, 'zz') -- +05
'zzz'	Hours and minutes offset from UTC. DECLARE @d DATETIMEOFFSET SET @d='6/15/2009 1:45:30 AM +5:30' SELECT FORMAT(@d, 'zzz') -- +05:30
--Date, Time Separator (Time Format)
':'	The time separator. SELECT FORMAT(GETDATE(), 'hh:mm:ss tt') -- 10:52:15 PM
'/'	The date separator. DECLARE @d DATETIME SET @d='5/9/2001 6:09:59 AM' SELECT FORMAT ( @d, 'yyyy/MM/dd hh:mm:ss tt', 'en-US' ); --2001/05/09 06:09:59 AM
--String
"string"	Literal string delimiter. DECLARE @d DATETIME SET @d='5/9/2001 6:09:59 AM' SELECT FORMAT ( @d, '"Today is "yyyy/MM/dd', 'en-US' ); --Today is 2001/05/09 SELECT FORMAT ( @d, '"Mail sent on: "yyyy/MM/dd' ); --Mail sent on: 2001/05/09
--Escape Character
\	The escape character. DECLARE @d DATETIME SET @d='5/9/2001 6:09:59 AM' SELECT FORMAT ( @d, 'h hour', 'en-US' ); --6 6our ,not as expectd SELECT FORMAT ( @d, 'h \hour' ); --6 hour SELECT FORMAT ( @d, 'MM \m' ); --05 m SELECT FORMAT ( @d, 'MM m' ); --05 9, not as expected
--Single Custom Format Specifier
%	Defines the following character as a custom format specifier. To use any of the custom date and time format specifiers as the only specifier in a format string (that is, to use the "d", "f", "F", "g", "h", "H", "K", "m", "M", "s", "t", "y", "z", ":", or "/" custom format specifier by itself), include a space before or after the specifier, or include a percent ("%") format specifier before the single custom date and time specifier. DECLARE @d DATETIME SET @d='5/9/2001 6:09:59 AM' SELECT FORMAT ( @d, '%h'); --6 SELECT FORMAT ( @d, 'h'); --NULL
--Invalid Format
Any Other Character	The character is copied to the result string unchanged. DECLARE @d DATETIME SET @d='5/9/2001 6:09:59 AM' SELECT FORMAT ( @d, ' invalid h'); -- invali9 6 SELECT FORMAT ( @d, 'Qui\t in h \hour'); --Quit in 6 hour
	Note : Use any combination of custom format string to customize your date and time format requirements.

PARSE()

Converts string value to a requested data type. If convertion is not possible to the requested data type then PARSE will throw an error. Use PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value. PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR).

Syntax :-

PARSE ( string_value AS data_type [ USING culture ] )

Where string_value is an nvarchar (4000) value representing the formatted value to parse into the specified data type. string_value must be a valid representation of the requested data type. or else PARSE raises an error. data_type is a literal value representing the data type requested for the result. culture is an optional string that identifies the culture in which string_value is formatted. If the culture argument is not provided, then the language of the current session is used. If the culture argument is not valid then PARSE raises an error.

We can see some sample code for parsing date and or time.

-- If the culture argument isn't provided, the language of current session is used.
SELECT PARSE('04/26/2013 16:30:59' AS date) AS 'date'; -- 2013-04-26
SELECT PARSE('04/26/2013 16:30:59' AS time) AS 'time'; -- 16:30:59.0000000
SELECT PARSE('04/26/2013 16:30:59' AS datetime) AS 'datetime'; -- 2013-04-26 16:30:59.000
SELECT PARSE('04/26/2013 16:30:59' AS smalldatetime) AS 'smalldatetime'; -- 2013-04-26 16:31:00
SELECT PARSE('04/26/2013 16:30:59' AS datetime2) AS 'datetime2'; -- 2013-04-26 16:30:59.0000000
SELECT PARSE('04/26/2013 16:30:59' AS datetimeoffset) AS 'datetimeoffset'; -- 2013-04-26 16:30:59.0000000 +00:00

SELECT PARSE('04/26/2013' AS date) AS 'date'; -- 2013-04-26
SELECT PARSE('04/26/2013' AS time) AS 'time'; -- 00:00:00.0000000
SELECT PARSE('04/26/2013' AS datetime) AS 'datetime'; -- 2013-04-26 00:00:00.000
SELECT PARSE('04/26/2013' AS smalldatetime) AS 'smalldatetime'; -- 2013-04-26 00:00:00
SELECT PARSE('04/26/2013' AS datetime2) AS 'datetime2'; -- 2013-04-26 00:00:00.0000000
SELECT PARSE('04/26/2013' AS datetimeoffset) AS 'datetimeoffset'; -- 2013-04-26 00:00:00.0000000 +00:00

SELECT PARSE('16:30:59' AS date) AS 'date'; -- 2013-04-27
SELECT PARSE('16:30:59' AS time) AS 'time'; -- 16:30:59.0000000
SELECT PARSE('16:30:59' AS datetime) AS 'datetime'; -- 2013-04-27 16:30:59.000
SELECT PARSE('16:30:59' AS smalldatetime) AS 'smalldatetime'; -- 2013-04-27 16:31:00
SELECT PARSE('16:30:59' AS datetime2) AS 'datetime2'; -- 2013-04-27 16:30:59.0000000
SELECT PARSE('16:30:59' AS datetimeoffset) AS 'datetimeoffset'; -- 2013-04-27 16:30:59.0000000 +00:00

-- Parse to datetime specifying culture in which string value is formatted
SELECT PARSE('Friday, 26 April 2013' AS date USING 'en-US') AS 'date'; -- 2013-04-26
SELECT PARSE('Friday, 26 April 2013' AS time USING 'en-US') AS 'time'; -- 00:00:00.0000000
SELECT PARSE('Friday, 26 April 2013' AS datetime USING 'en-US') AS 'datetime'; -- 2013-04-26 00:00:00.000
SELECT PARSE('Friday, 26 April 2013' AS smalldatetime USING 'en-US') AS 'smalldatetime'; -- 2013-04-26 00:00:00
SELECT PARSE('Friday, 26 April 2013' AS datetime2 USING 'en-US') AS 'datetime2'; -- 2013-04-26 00:00:00.0000000
SELECT PARSE('Friday, 26 April 2013' AS datetimeoffset USING 'en-US') AS 'datetimeoffset'; -- 2013-04-26 00:00:00.0000000 +00:00

SELECT PARSE('04/26/2013 16:30:59' AS date USING 'en-US') AS 'date'; -- 2013-04-26
SELECT PARSE('04/26/2013 16:30:59' AS time USING 'en-US') AS 'time'; -- 16:30:59.0000000
SELECT PARSE('04/26/2013 16:30:59' AS datetime USING 'en-US') AS 'datetime'; -- 2013-04-26 16:30:59.000
SELECT PARSE('04/26/2013 16:30:59' AS smalldatetime USING 'en-US') AS 'smalldatetime'; --2013-04-26 16:31:00
SELECT PARSE('Samstag, 27. April 2013' AS datetime2 USING 'de-DE') AS 'datetime2'; -- 2013-04-27 00:00:00.0000000
SELECT PARSE('sábado, 27 de Abril de 2013' AS datetimeoffset USING 'pt-PT') AS 'datetimeoffset'; -- 2013-04-27 00:00:00.0000000 +00:00

-- PARSE throws error for non convertable value
SELECT PARSE('WRONGVALUE' AS date) AS 'date'; -- Error, 'Error converting string value 'WRONGVALUE' into data type date using culture ''.'

TRY_PARSE()

Converts string value to a requested data type. If convertion is not possible to the requested data type then TRY_PARSE will return NULL. Use TRY_PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value. TRY_PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR).

Syntax :-

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Parameters are similar to PARSE, All The example of PARSE() will work as it is except the below one,

SELECT TRY_PARSE('WRONGVALUE' AS date) AS 'date'; -- NULL

Sample :-

--If the culture argument isn't provided, the language of current session is used.
SELECT TRY_PARSE('Friday, 26 April 2013' AS date) AS 'date'; -- 2013-04-26
-- Parse to datetime specifying culture in which string value is formatted
SELECT TRY_PARSE('04/26/2013 16:30:59' AS smalldatetime USING 'en-US') AS 'smalldatetime'; --2013-04-26 16:31:00
SELECT TRY_PARSE('Samstag, 27. April 2013' AS datetime2 USING 'de-DE') AS 'datetime2'; -- 2013-04-27 00:00:00.0000000
SELECT TRY_PARSE('sábado, 27 de Abril de 2013' AS datetimeoffset USING 'pt-PT') AS 'datetimeoffset'; -- 2013-04-27 00:00:00.0000000 +00:00
SELECT TRY_PARSE('Friday, 26 April 2013' AS datetimeoffset USING 'en-US') AS 'datetimeoffset'; -- 2013-04-26 00:00:00.0000000 +00:00
-- PARSE throws error for non convertable value
SELECT TRY_PARSE('WRONGVALUE' AS date) AS 'date'; -- NULL

TRY_CONVERT()

TRY_CONVERT function is very similar to the CONVERT function except that it returns NULL when the conversion cannot be completed.

Syntax :--

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

TRY_CONVERT parameters are same to CONVERT function.

Sample :-

SELECT TRY_CONVERT(date, 'WRONGVALUE') AS 'date' -- NULL

References

The reference links from MSDN are given below.

Summary

In this article I have explained Functions that helps to convert date and time values to and from string literals and other date and time formats. If I have missed anything or need any change in definition then please let me know. I hope you have enjoyed this article and got some value addition to your knowledge.

You might be interested in the below articles.

I have put my time and efforts on all of my articles, Please don't forget to mark your votes, suggestions and feedback to improve the quality of this and upcoming articles. Thanks for reading.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Shemeer NS

Software Developer (Senior)

India

Member

Technology Specialist | CodeProject MVP | Author | Geek | Netizen | Husband | ChessPlayer

Most of my articles are listed on top 5 of the respective 'Best articles of the month' and some of my articles are published on ASP.NET WebSite's Article of the Day section.

Check my contributions in Visual Studio Gallery and Code Project

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

CAST(), CONVERT(), PARSE(), TRY_PARSE(), TRY_CONVERT(), FORMAT(), SQL Server Functions, SQL Server Functions that helps to convert date and time values to and from string literals and other date and time formats.

Type	Article
Licence	CPOL
First Posted	5 May 2013
Views	3,848
Downloads	98
Bookmarked	22 times

SQL2000SQL2005
SQL-ServerDev, +

SQL Server Functions that helps to convert date and time values to and from string literals and other date and time formats.

Table Of Contents

Introduction

CAST()

CONVERT()

Converting DATETIME to VARCHAR

Converting VARCHAR to DATETIME

Date and Time Styles

FORMAT()

Standard Date and Time Format Strings

Custom Date and Time Format Strings

PARSE()

TRY_PARSE()

TRY_CONVERT()

References

Summary

License

About the Author

Comments and Discussions

		9,866,691 members (26,336 online) Visit CodeProject.TV Discuss CodeProject.TV Sign in Email Password Forgot your password? Sign in using