Transact SQL:- Format Function – Dates

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

id goal format formatted
1 date ( short ) d 3/1/2022
2 date ( long ) D Tuesday, March 1, 2022
3 day of the week – short ddd Tue
4 day of the week – full dddd Tuesday
5 day of month ( added space to differentiate from d on its own ) d 1
6 day of month ( left padded with 0s ) dd 01
7 Month Number ( left padded with 0s ) MM 03
8 Month Name Abbreviated MMM Mar
9 Month Name MMMM March
10 [Month Name] [Day of the Month] m March 1
11 [Month Name] [Day of the Month] M March 1
12 Year ( 2 Digits Year) yy 22
13 Year ( 4 Digits Year) yyyy 2022
14 Hour ( 12 to 11 ) hh 12
15 Hour ( 00 to 23 ) HH 00
16 Minute ( 00 to 59 ) mm 50
17 Second ( 00 to 59 ) ss 00
18 AM/PM tt AM

 

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

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s