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()))
#SQLServer