SQL SERVER – Get Time in Hour:Minute Format from a Datetime and CONVERT() | How to get Date or month or year Part Only from Datetime

Time format conversion in SQL Server

We can convert getdate() to different formats. In the below queries i have used convert function for doing this operation.

select convert(time,getdate()) as Time
select CONVERT(date,getdate()) as date

select convert( varchar , getdate() , 101 ) as [mm/dd/yyyy]
select convert( varchar , getdate() , 102 ) as [yy.mm.dd]
select convert( varchar , getdate() , 103 ) as [dd/mm/yy]
select convert( varchar , getdate() , 104 ) as [dd.mm.yy]
select convert( varchar , getdate() , 105 ) as [dd-mm-yy]
select convert( varchar , getdate() , 106 ) as [dd mon yy]
select convert( varchar , getdate() , 107 ) as [Mon dd, yy]
select convert( varchar , getdate() , 108 ) as [hh:mm:ss]
select convert( varchar , getdate() , 109 ) as [mon dd yyyy hh:mi:ss:mmmAM (or PM)]
select convert( varchar , getdate() , 110 ) as [mm-dd-yy]
select convert( varchar , getdate() , 111 ) as [yy/mm/dd]
select convert( varchar , getdate() , 112 ) as [yymmdd]
select convert( varchar , getdate() , 113 ) as [dd mon yyyy hh:mm:ss:mmm(24h)]
select convert( varchar , getdate() , 114 ) as [hh:mi:ss:mmm(24h)]

This is one of the good SQL Server interview Questions.