MSSQL - SELECT Date in a specific format (yyyy-MM-dd, MM/dd/yy, MM-dd/yyyy etc.)

on MSSQL

In MSSQL you can easily format datetime strings using the FORMAT function. For the examples in this post we will use a simple table like the following:

CREATE TABLE SomeTable(
  Id INT IDENTITY(1,1) PRIMARY KEY (ID) NOT NULL,
  [CreationTime] DATETIME2 NOT NULL,
)

INSERT INTO [dbo].[SomeTable] ([CreationTime]) VALUES (GETDATE());

For the following examples we will use a DateTime I created with the value of 2022-10-30 12:28:40.3466667 shown with format yyyy-MM-dd HH:mm:ss.FFFFFFF. We can get the format yyyy-MM-dd (2022-10-30) using the following query:

SELECT FORMAT([CreationTime], 'yyyy-MM-dd') AS CreationTime
  FROM [NewDb].[dbo].[SomeTable]

We can get the format MM/dd/yy (10/30/22) using the following query:

SELECT FORMAT([CreationTime], 'MM/dd/yy') AS CreationTime
  FROM [NewDb].[dbo].[SomeTable]

Or any combination such as MM-dd/yyyy (10-30/2022):

SELECT FORMAT([CreationTime], 'MM-dd/yyyy') AS CreationTime
  FROM [NewDb].[dbo].[SomeTable]

We can get all the numeric value components of the datetime using the format yyyy-MM-dd HH:mm:ss.FFFFFFF:

SELECT FORMAT([CreationTime], 'yyyy-MM-dd HH:mm:ss.FFFFFFF') AS CreationTime
  FROM [NewDb].[dbo].[SomeTable]

You can use this short list for reference:

  • yyyy - The full year as a 4 digit number.
  • yy - The last two digits of the year, 2022 becomes 22.
  • MM - The numeric month of the Datetime (1-12).
  • dd - The numeric day of the DateTime.
  • HH - The hour of the Datetime shown with a 24 hour clock.
  • hh - The hour of the Datetime shown with a 12 hour clock.
  • mm - The minutes of the Datetime
  • ss - The seconds of the Datetime
  • FFFFFF - The millionths of a second of the Datetime. You can adjust how many digits you want with the amount of F's.

The full list of format specifiers can be found here.

That is all

I hope you found this helpful in formatting your SQL datetimes. Please leave a comment down below if you have any further questions or want to add to the above!