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