What are the different types of Date formats in SQL Server?

GETDATE

The GETDATE function returns the current system date and time. The syntax is as follows:

GETDATE()

Example:

Select GETDATE() as CurrentDate

Output:

2013-06-23 21:55:40.113

Now i'll explain how to get different date formats.

Different Date format examples:

Select Datename(day,getdate())+'-'+substring(Datename(month,getdate()),1,3)+'-'+Datename(year,getdate())

Output:

24-Jun-2013

Select CONVERT(varchar, getdate(), 101) as [Date Format]

Output:

06/23/2013

Select CONVERT(varchar, getdate(), 102) as [Date Format]

Output:

2013.06.23

Select CONVERT(varchar, getdate(), 103) as [Date Format]

Output:

23/06/2013

Select CONVERT(varchar, getdate(), 104) as [Date Format]

Output:

23.06.2013

Select CONVERT(varchar, getdate(), 105) as [Date Format]

Output:

 23-06-2013

Select CONVERT(varchar, getdate(), 106) as [Date Format]

Output:

23 Jun 2013

Select CONVERT(varchar, getdate(), 107) as [Date Format]

Output:

Jun 23, 2013

Select CONVERT(varchar, getdate(), 108) as [Date Format]

Output:

21:40:18

Select CONVERT(varchar, getdate(), 109) as [Date Format]

Output:

Jun 23 2013  9:40:18:200PM

Select CONVERT(varchar, getdate(), 110) as [Date Format]

Output:

06-23-2013

Select CONVERT(varchar, getdate(), 111) as [Date Format]

Output:

2013/06/23

Select CONVERT(varchar, getdate(), 112) as [Date Format]

Output:

20130623

Select CONVERT(varchar, getdate(), 113) as [Date Format]

Output:

23 Jun 2013 21:40:18:200

Select CONVERT(varchar, getdate(), 114) as [Date Format]

Output:

21:40:18:200