Date Format using CONVERT function in SQL Server

Today, i would like to list out various styles of date display formats. These are very helpful for the Front-end application programmers where they want to display different date formats.

Various date style formats can be produced with CONVERT function.
CONVERT function need 3 parameters.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
The first parameter is return data type, second parameter is expression, third parameter which is optional parameter defines the style.

NOTE: Do not apply CONVERT function on date column when it is Indexed.
with out waiting lets look at the various styles of date values.
The below query produces the output in
MON DD YYYY HH:MIAMPM format.
SELECT CONVERT(VARCHAR(30),GETDATE(),100)
--Output (MON DD YYYY HH:MIAMPM)
--Jul 7 2009 2:19PM
The below query produces the output in MM/DD/YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),101)
--Output (MM/DD/YYYY)
--07/07/2009
The below query produces the output in YYYY.MM.DD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),102)
--Output (YYYY.MM.DD)
--2009.07.07
The below query produces the output in DD/MM/YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),103)
--Output (DD/MM/YYYY)
--06/07/2009
The below query produces the output in DD.MM.YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),104)
--Output (DD.MM.YYYY)
--06.07.2009
The below query produces the output in DD-MM-YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),105)
--Output (DD-MM-YYYY)
--06-07-2009
The below query produces the output in DD MON YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),106)
--Output (DD MON YYYY)
--06 Jul 2009
The below query produces the output in MON DD,YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),107)
--Output (MON DD,YYYY)
--Jul 06, 2009
The below query produces the output in HH24:MI:SS format.
SELECT CONVERT(VARCHAR(30),GETDATE(),108)
--Output (HH24:MI:SS)
--14:24:20
The below query produces the output in MON DD YYYY HH:MI:SS:NNN AMPM format.
Use 113 style to get date and time with nano seconds in AM/PM format.
SELECT CONVERT(VARCHAR(30),GETDATE(),109)
--Output (MON DD YYYY HH:MI:SS:NNN AMPM)
--Jul 7 2009 2:24:35:490PM
The below query produces the output in MM-DD-YYYY format.
SELECT CONVERT(VARCHAR(30),GETDATE(),110)
--Output (MM-DD-YYYY)
-- 07-07-2009
The below query produces the output in YYYY/MM/DD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),111)
--Output (YYYY/MM/DD)
--2009/07/07
The below query produces the output in YYYYMMDD format.
SELECT CONVERT(VARCHAR(30),GETDATE(),112)
--Output (YYYYMMDD)
--20090707
The below query produces the output in MON DD YYYY HH24:MI:SS:NNN format.
Use 113 style to get date and time with nano seconds in 24 Hour Format.
SELECT CONVERT(VARCHAR(30),GETDATE(),113)
--Output (MON DD YYYY HH24:MI:SS:NNN)
--07 Jul 2009 14:26:24:617
The below query produces the output in HH24:MI:SS:NNN format.
Use 114 Style to extract Time part with nano seconds in 24 Hour Format.
SELECT CONVERT(VARCHAR(30),GETDATE(),114)
--Output (HH24:MI:SS:NNN)
--14:26:48:953

Information Schema Views in SQL Server

In this article, I would like to list out the SQL Server metadata tables which we need in our day-to-day work.
SELECT * FROM Information_Schema.CHECK_CONSTRAINTS
-- Contains one row for each CHECK constraint in the current database.
SELECT * FROM Information_Schema.COLUMN_DOMAIN_USAGE
-- Contains one row for each column, in the current database, that has a user-defined data type.
SELECT * FROM Information_Schema.COLUMN_PRIVILEGES
--Contains one row for each column with a privilege either granted to or by the current user in the current database
SELECT * FROM Information_Schema.COLUMNS
--Contains one row for each column accessible to the current user in the current database.
SELECT * FROM Information_Schema.CONSTRAINT_COLUMN_USAGE
--Contains one row for each column, in the current database, that has a constraint defined on it.
SELECT * FROM Information_Schema.CONSTRAINT_TABLE_USAGE
--Contains one row for each table, in the current database, that has a constraint defined on it.
SELECT * FROM Information_Schema.DOMAIN_CONSTRAINTS
--Contains one row for each user-defined data type, accessible to the current user in the current database, with a rule bound to it.
SELECT * FROM Information_Schema.DOMAINS
--Contains one row for each user-defined data type accessible to the current user in the current database.
SELECT * FROM Information_Schema.KEY_COLUMN_USAGE
--Contains one row for each column, in the current database, that is constrained as a key.
SELECT * FROM Information_Schema.PARAMETERS
--Contains one row for each parameter of a user-defined function or stored procedure accessible to the current user in the current database.
SELECT * FROM Information_Schema.REFERENTIAL_CONSTRAINTS
--Contains one row for each foreign constraint in the current database.
SELECT * FROM Information_Schema.ROUTINE_COLUMNS
--Contains one row for each column returned by the table-valued functions accessible to the current user in the current database.
SELECT * FROM Information_Schema.ROUTINES
--Contains one row for each stored procedure and function accessible to the current user in the current database.
SELECT * FROM Information_Schema.SCHEMATA
--Contains one row for each database that has permissions for the current user.
SELECT * FROM Information_Schema.TABLE_CONSTRAINTS
--Contains one row for each table constraint in the current database.
SELECT * FROM Information_Schema.TABLE_PRIVILEGES
--Contains one row for each table privilege granted to or by the current user in the current database.
SELECT * FROM Information_Schema.TABLES
--Contains one row for each table in the current database for which the current user has permissions.
SELECT * FROM Information_Schema.VIEW_COLUMN_USAGE
--Contains one row for each column, in the current database, used in a view definition.
SELECT * FROM Information_Schema.VIEW_TABLE_USAGE
--Contains one row for each table, in the current database, used in a view.
SELECT * FROM Information_Schema.VIEWS
--Contains one row for views accessible to the current user in the current database.

Interview Questions

.Net Interview Questions
ASP.NETASP.Net Interview Questions - Part 1
ASP.Net Interview Questions - Part 2
ASP.Net Interview Questions - Part 3
ASP.Net Interview Questions - Part 4
ASP.Net Interview Questions - Part 5
ASP.Net Interview Questions - Part 6
ASP.Net Interview Questions - Part 7
ASP.Net Interview Questions - Part 8
ASP.Net Interview Questions - Part 9
ASP.Net Interview Questions - Part 10
ASP.Net Interview Questions - Part 11
ASP.Net Interview Questions - Part 12
C# C# Interview Questions