Background

Let us do a bit more with the format function.

In this post we will talk up the date datatype.

Previous Posts

1. Transact SQL:- Format Function
   Link

Format Function – Data Type – Date

Lab

Let us take this into the Lab.

Here is the SQL we used to produce the result above.

set nocount on
go

declare @ts datetime 

declare @tblDateTime table
(

      [id]     smallint not null
               identity(1,1)

    , [goal]   varchar(100) not null

    , [format] varchar(100) not null
    
)

set @ts = getdate()

set @ts = '2022-03-01 00:50'
--set @ts = '2022-03-01 12:50:22 pm'

/*
    set @ts = '2022-03-01 23:45'
*/

insert into @tblDateTime
(
      [goal]
    , [format]
)
values
  ( 'date ( short )', 'd')
, ( 'date ( long )',  'D')
/*
    Day of the week
*/
, ( 'day of the week - short', 'ddd')
, ( 'day of the week - full', 'dddd')

/*
    Day of the Month
*/
, ( 'day of month ( added space to differentiate from d on its own )', ' d')
, ( 'day of month ( left padded with 0s )', 'dd')

/*
    Month Number
*/
, ( 'Month Number ( left padded with 0s )', 'MM')

/*
    Month Name
*/
, ( 'Month Name Abbreviated', 'MMM')
, ( 'Month Name', 'MMMM')

, ( '[MonthName] [Day of the Month]', 'm')
, ( '[MonthName] [Day of the Month]', 'M')

/*
    Year
*/
, ( 'Year ( 2 Digits Year)', 'yy')
, ( 'Year ( 4 Digits Year)', 'yyyy')

/*
    Hour
*/
, ( 'Hour ( 12 to 11 )', 'hh')
, ( 'Hour ( 00 to 23 )', 'HH')

/*
    Minute
*/
, ( 'Minute ( 00 to 59 )', 'mm')

/*
    Second
*/
, ( 'Second ( 00 to 59 )', 'ss')

/*
    AM/PM
*/
, ( 'AM/PM', 'tt')

select 
          tblDT.[id]

        , tblDT.[goal]

        , tblDT.[format]

        , [formatted]
            = format
              (
                  @ts
                , tblDT.[format]
             )

from   @tblDateTime tblDT

Source Code Control

GitLab

Transact-SQL Format Function

transactSQLFunctionFormatDate.sql
Link