SELECT CONVERT(VARCHAR(10), GETDATE(), 110) what is the meaning of 110 here?

Question

When we convert or cast date in sql, see below sql code

SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 

it works fine, I just want to know the meaning of 110 in above code. what it does actually, sometimes we use 102, 112 etc. what is the use of that number.

Solution 1

That number indicates Date and Time Styles

You need to look at CAST and CONVERT (Transact-SQL). Here you can find the meaning of all these Date and Time Styles.

Styles with century (e.g. 100, 101 etc) means year will come in yyyy format. While styles without century (e.g. 1,7,10) means year will come in yy format.

You can also refer to SQL Server Date Formats. Here you can find all date formats with examples.

Solution 2

10 = mm-dd-yy 110 = mm-dd-yyyy

SQL Server CONVERT() Function

Solution 3

110 is the Style value for the date format.

TSQL Date and Time Styles

Solution 4

When you convert expressions from one type to another, in many cases there will be a need within a stored procedure or other routine to convert data from a datetime type to a varchar type. The Convert function is used for such things. The CONVERT() function can be used to display date/time data in various formats.

Syntax

CONVERT(data_type(length), expression, style)

Style - style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

Example 1

take a style value 108 which defines the following format:

hh:mm:ss

Now use the above style in the following query:

select convert(varchar(20),GETDATE(),108) 

Example 2

we use the style value 107 which defines the following format:

Mon dd, yy

Now use that style in the following query:

select convert(varchar(20),GETDATE(),107) 

Similarly

style-106 for Day,Month,Year (26 Sep 2013)
style-6 for Day, Month, Year (26 Sep 13)
style-113 for Day,Month,Year, Timestamp (26 Sep 2013 14:11:53:300)

View more solutions

Comments

[Michael Freidgeim]

Possible duplicate of How to convert DateTime to VarChar