SQL Server - Function to format date field with Convert command

Question

I have the following function:

select convert(DATE,GETDATE(),101)

Where she returns to me as follows: 2021-08-17

I want him to return me a data with this format "17/8/2021" keeping the data as date.

Answer 1

When using the function convert, is using a date display style. Recommended is to use varchar in this situation to present the information. To manipulate the record with the type DATE, must manipulate before making the conversion. See an example:

DECLARE @d DATE = GETDATE();
Select convert(varchar, @d, 103) as hoje, convert(varchar, dateadd(day,1,@d), 103) as amanhã

Upshot:

today	tomorrow
18/08/2021	19/08/2021

The function convert() is used to display information. See that to calculate the date amanhã, the date with the original form was first used date in function dateadd() and then the result was converted to the desired display form with the convert().

Another option to display in dd/MM/yyyy format:

DECLARE @d DATE = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'pt-BR' ) AS 'Data' 

Upshot:

Date
18/08/2021

Other date styles:

select 'convert(varchar, getdate(), 1)' as 'Consulta', convert(varchar, getdate(), 1) as 'Resultado' union
select 'convert(varchar, getdate(), 2)', convert(varchar, getdate(), 2) union
select 'convert(varchar, getdate(), 3)', convert(varchar, getdate(), 3) union
select 'convert(varchar, getdate(), 4)', convert(varchar, getdate(), 4) union
select 'convert(varchar, getdate(), 5)', convert(varchar, getdate(), 5) union
select 'convert(varchar, getdate(), 6)', convert(varchar, getdate(), 6) union
select 'convert(varchar, getdate(), 7)', convert(varchar, getdate(), 7) union
select 'convert(varchar, getdate(), 10)', convert(varchar, getdate(), 10) union
select 'convert(varchar, getdate(), 11)', convert(varchar, getdate(), 11) union
select 'convert(varchar, getdate(), 12)', convert(varchar, getdate(), 12) union
select 'convert(varchar, getdate(), 23)', convert(varchar, getdate(), 23) union
select 'convert(varchar, getdate(), 101)', convert(varchar, getdate(), 101) union
select 'convert(varchar, getdate(), 102)', convert(varchar, getdate(), 102) union
select 'convert(varchar, getdate(), 103)', convert(varchar, getdate(), 103) union
select 'convert(varchar, getdate(), 104)', convert(varchar, getdate(), 104) union
select 'convert(varchar, getdate(), 105)', convert(varchar, getdate(), 105) union
select 'convert(varchar, getdate(), 106)', convert(varchar, getdate(), 106) union
select 'convert(varchar, getdate(), 107)', convert(varchar, getdate(), 107) union
select 'convert(varchar, getdate(), 110)', convert(varchar, getdate(), 110) union
select 'convert(varchar, getdate(), 111)', convert(varchar, getdate(), 111) union
select 'convert(varchar, getdate(), 112)', convert(varchar, getdate(), 112)

Upshot:

Consultation	Upshot
Convert(varchar, getdate(), 1)	08/18/21
Convert(varchar, getdate(), 10)	08-18-21
Convert(varchar, getdate(), 101)	08/18/2021
Convert(varchar, getdate(), 102)	2021.08.18
Convert(varchar, getdate(), 103)	18/08/2021
Convert(varchar, getdate(), 104)	18.08.2021
Convert(varchar, getdate(), 105)	18-08-2021
Convert(varchar, getdate(), 106)	18 Aug 2021
Convert(varchar, getdate(), 107)	Aug 18, 2021
Convert(varchar, getdate(), 11)	21/08/18
Convert(varchar, getdate(), 110)	08-18-2021
Convert(varchar, getdate(), 111)	2021/08/18
Convert(varchar, getdate(), 112)	20210818
Convert(varchar, getdate(), 12)	210818
Convert(varchar, getdate(), 2)	21.08.18
Convert(varchar, getdate(), 23)	2021-08-18
Convert(varchar, getdate(), 3)	18/08/21
Convert(varchar, getdate(), 4)	18.08.21
Convert(varchar, getdate(), 5)	18-08-21
Convert(varchar, getdate(), 6)	18 Aug 21
Convert(varchar, getdate(), 7)	Aug 18, 21

Read more about the functions Convert, Cast and Format to Sqlserver in:

1. https://docs.microsoft.com/pt-br/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
2. https://docs.microsoft.com/pt-br/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15