SQL Server Date Formatting and Date Examples

–Get Current Date and Time
select getdate()

–Date Format MMM DD YYYY
select convert(nvarchar(12),getdate(),100)

–Date Format MM/DD/YYYY
select convert(nvarchar(10),getdate(),101)

–Date Format YYYY.MM.DD
select convert(nvarchar(10),getdate(),102)

–Date Format DD/MM/YYYY
select convert(nvarchar(10),getdate(),103)

–Date Format DD.MM.YYYY
select convert(nvarchar(10),getdate(),104)

–Date Format DD-MM-YYYY
select convert(nvarchar(10),getdate(),105)

–Date Format DD MMM YYYY
select convert(nvarchar(11),getdate(),106)

–Date Format MMM DD, YYYY
select convert(nvarchar(12),getdate(),107)

–Date Format hh:mm:ss (24hr)
select convert(nvarchar(10),getdate(),108)

–Date Format hh:mm:ss:mmm (AM PM)
select convert(nvarchar(30),getdate(),109)

–Date Format MM-DD-YYYY (AM PM)
select convert(nvarchar(10),getdate(),110)

–Date Format YYYY/MM/DD
select convert(nvarchar(10),getdate(),111)

–Date Format YYYYMMDD
select convert(nvarchar(10),getdate(),112)

–Date Format DD MMM YYYY hh:mm:ss:mmm(24h)
select convert(nvarchar(24),getdate(),113)

–Date Format hh:mm:ss:mmm(24h)
select convert(nvarchar(12),getdate(),114)

–Date Format hh:mm:ss:mmm(24h)
select convert(nvarchar(19),getdate(),120)

–Date Format YYYY-MM-DD hh:mm:ss:mmm(24h)
select convert(nvarchar(23),getdate(),121)

–Date Format YYYY-MM-DDThh:mm:ss:mmm(24h)
select convert(nvarchar(24),getdate(),126)

–Time Format hh:mm PM
select substring(convert(varchar(20), GetDate(), 109), 13, 5) + ‘ ‘ + substring(convert(varchar(30), GetDate(), 109), 25, 2)

–Time Format hh:mm:ss PM
select substring(convert(varchar(20), GetDate(), 109), 13, 8) + ‘ ‘ + substring(convert(varchar(30), GetDate(), 109), 25, 2)

–Date Format YYYY.MM.DD
select convert(nvarchar(10),getdate(),102)

–Date Get Month
select month(getdate())

–Date Get Numerical Day of Month
select day(getdate())

–Date Get Year
select year(getdate())

–Date Get Last Day of Current Month
select convert(nvarchar(10),dateadd(day,-1,convert(nvarchar(2),month(dateadd(month,1,getdate()))) + ‘/1/’ + convert(nvarchar(4),year(dateadd(month,1,getdate())))),101)

–Date Get First Day of Current Month
select convert(nvarchar(10),cast(convert(varchar(2),month(getdate())) + ‘/01/’ + convert(varchar(4),year(getdate())) as datetime),101)

–Date Get Last Day of Current Year
select ’12/31/’ + convert(nvarchar(4),year(dateadd(month,1,getdate())))

–Date Get First Day of Current Year
select ’01/01/’ + convert(varchar(4),year(getdate()))

Advertisements
About

Over 20 years programming experience 5 years Oracle SQL/Stored Procedure programming Over 10 years SQL 2000/2005/2008 Server SQL/Stored Procedure programming Over 18 years Visual Basic 3.0/4.0/5.0/6.0/.Net/2003/2005/2008/2010/2015 Over 10 years ASP.Net Over 7 years Visual C# Over 20 years Microsoft Office and VBA HTML JAVA COBOL EASYTRIEVE FORTRAN

Tagged with: , , , , , ,
Posted in SQL Server
One comment on “SQL Server Date Formatting and Date Examples
  1. […] with great flexibility often comes confusion. It is helpful to see a few simple examples. In this article from Greg Owen, we see several examples of ways to display the current date and […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: