Greg Owen
Over 27 Years of Programming Experience
Programming Examples, Portfolio, and More
Home

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
Some words from the sponsors...

Add a C# Dropdown to a Bootstrap Modal Dialog

This example combines C# and Bootstrap to create a bootstrap modal dialog containing an asp dropdown and an asp button to retrieve...

Continue Reading

Use C# to Populate Dropdown Lists With XML

The code below opens an xml file, reads the data from the xml file, dynamically adds a label for the dropdown list, and adds the dropdown list to the asp.net page...

Continue Reading

Add Style Properties to ASP.Net Controls to Improve Appearance

The default formatting for ASP.Net controls is very dated.  The default formatting is basically the same as the formatting in Visual Basic 1.0 ...

Continue Reading

Export To A Text File From A DataGridView Control

Add a button control to Form1.
Add a DataGridView Control to Form1....

Continue Reading