Home › SQL Server › SQL Server Date Formatting and Date Examples

SQL Server Date Formatting and Date Examples

Posted on February 9, 2012 by Greg Owen — 1 Comment

–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()))

About

Over 27 years programming experience 17 years Oracle SQL/Stored Procedure programming Over 17 years SQL 2000/2005/2008/2012/2016 Server SQL/Stored Procedure programming Over 25 years Visual Basic 3.0/4.0/5.0/6.0/.Net/2003/2005/2008/2010/2015/2019 Over 19 years ASP.Net Over 17 years Visual C# Over 25 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. Date Formatting Examples in SQL Server – Senior DBA says:
    September 6, 2016 at 9:02 am

    […] 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 […]

    Reply

Leave a comment