SQL Date Format Options with SQL CONVERT Function

DECLARE @counter INT = 0
DECLARE @date DATETIME = '2006-12-30 00:38:54.840'

CREATE TABLE #dateFormats (dateFormatOption int, dateOutput nvarchar(40))

WHILE (@counter <= 150 )
BEGIN
   BEGIN TRY
      INSERT INTO #dateFormats
      SELECT CONVERT(nvarchar, @counter), CONVERT(nvarchar,@date, @counter) 
      SET @counter = @counter + 1
   END TRY
   BEGIN CATCH;
      SET @counter = @counter + 1
      IF @counter >= 150
      BEGIN
         BREAK
      END
   END CATCH
END

SELECT * FROM #dateFormats

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--dd/mm/yyyy with 4 DIGIT YEAR
SELECT CONVERT(VARCHAR(10), @Datetime, 103) CurrentDateFormattedAsText;
 
--dd/mm/yy with 2 DIGIT YEAR
SELECT CONVERT(VARCHAR(8), @Datetime,   3) CurrentDateFormattedAsText;


-- pull data from a database table
-- The date used for this example was January 25, 2013. 
--SELECT a datetime column as a string formatted dd/mm/yyyy (4 digit year)
SELECT TOP 3 CONVERT(CHAR(10), ExpectedDeliveryDate, 103) ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted dd/mm/yy (2 digit year)
SELECT TOP 3 CONVERT(CHAR(8), ExpectedDeliveryDate, 3) ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

-- The date used for this example was November 12, 2023.

DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--mm/dd/yyyy with 4 DIGIT YEAR
SELECT CONVERT(VARCHAR(10), @Datetime, 101) CurrentDateFormattedAsText;
 
--mm/dd/yy with 2 DIGIT YEAR
SELECT CONVERT(VARCHAR(8), @Datetime,   1) CurrentDateFormattedAsText;


-- pull data from a database table
-- The date used for this example was January 15, 2013.
--SELECT a datetime column as a string formatted mm/dd/yyyy (4 digit year)
SELECT TOP 3 CONVERT(CHAR(10), ExpectedDeliveryDate, 101) ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted mm/dd/yy (2 digit year)
SELECT TOP 3 CONVERT(CHAR(8), ExpectedDeliveryDate, 1) ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--yyyy mm dd with 4 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 102), '.', ' ') CurrentDateFormattedAsText;
 
--yy mm dd  with 2 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(8), @Datetime,   2), '.', ' ') CurrentDateFormattedAsText;


-- pull data from a database table
-- The date used for this example was January 15, 2013.
--SELECT a datetime column as a string formatted yyyy mm dd (4 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 102), '.', ' ') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted yy mm dd (2 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 2), '.', ' ') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--yyyymmdd with 4 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 112), '.', ' ') CurrentDateFormattedAsText;
 
--yymmdd  with 2 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(8), @Datetime,  12), '.', ' ') CurrentDateFormattedAsText;


-- pull data from a database table
-- The date used for this example was January 15, 2013.
--SELECT a datetime column as a string formatted yyyymmdd (4 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 112), '.', ' ') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted yymmdd (2 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 12), '.', ' ') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--ddmmyyyy with 4 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 104), '.', '') CurrentDateFormattedAsText;
 
--ddmmyy  with 2 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(8), @Datetime,   4), '.', '') CurrentDateFormattedAsText;


-- pull data from a database table
-- The date used for this example was January 15, 2013.
--SELECT a datetime column as a string formatted ddmmyyyy (4 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 104), '.', '') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted ddmmyy (2 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate,  4), '.', '') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--yyyy-mm-dd with 4 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 111), '/', '-') CurrentDateFormattedAsText;
 
--yy-mm-dd  with 2 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(8), @Datetime,   11), '/', '-') CurrentDateFormattedAsText;


-- pull data from a database table
-- The date used for this example was January 15, 2013.
--SELECT a datetime column as a string formatted yyyy-mm-dd (4 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 111), '/', '-') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted yy-mm-dd (2 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 11), '/', '-') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;

-- The date used for this example was November 12, 2023.
DECLARE @Datetime DATETIME;
SET @Datetime = GETDATE();
 
--mm/dd/yyyy with 4 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 110), '/', '-') CurrentDateFormattedAsText;
 
--mm/dd/yy  with 2 DIGIT YEAR
SELECT REPLACE(CONVERT(VARCHAR(8), @Datetime,   10), '/', '-') CurrentDateFormattedAsText;


-- pull data from a database table
-- The date used for this example was January 15, 2013. 
--SELECT a datetime column as a string formatted mm/dd/yyyy (4 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 110), '/', '-') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;
 
--SELECT a datetime column as a string formatted mm/dd/yy (2 digit year)
SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 10), '/', '-') ExpectedDeliveryDateFormattedAsText
FROM Purchasing.PurchaseOrders
WHERE OrderDate < @Datetime;