sqlsql-serverdateformatsql-convert

SQL Format and Convert functions in a select statement

I have a select and I need to format a few of it´s dates (in BBDD they are in YYYY-MM-DD and I need them in DD/MM/YYYY).

Looking around I found two different methods (both work OK)

1: FORMAT(pb.FINICIO, 'dd/MM/yyyy') as finicio

2: CONVERT(VARCHAR(10), pb.FFIN, 103) AS [DD/MM/YYYY]

This gives me some questions:

  1. What are the main differences between using a |FORMATor aCONVERT` in a select statement.

  2. While it may be opinion based, which option is better? Again, I am doing a select.

The whole query:

SELECT pb.IDFACT, pa.IDGRUPO, pa.CNOM, pa.CDESC
    , pa.CESTAD, pa.CABS, pa.FINICIO as finiciog
    , pa.FFIN as ffing, pa.NMOV45, pc.TIP032
    , pc.NIF032, pc.NOC032, pc.FCO032
    , pc.XVIA032, pc.XNOVIA32, pc.NVIA1032
    , pc.CPO032, pc.EMA032, FORMAT(pb.FINICIO, 'dd/MM/yyyy') as finicio
    , CONVERT(VARCHAR(10), pb.FFIN, 103) AS [DD/MM/YYYY], 
pc.TEL032, pc.OBS032, pc.SIT032
    , CONCAT(pc.ap1032 ,' ' ,pc.ap2032 , ', ' , pc.no032) as nombreCompleto, pc.noc032 as nombreCorporativo 
FROM spt145 as pa 
LEFT JOIN SPT144 as pb ON pa.IDGRUPO = pb.IDGRUPO and pb.SVIGE = 'S' 
LEFT JOIN GACTB032 as pc ON pb.idFact = pc.FACT032
WHERE pa.SVIGE = 'S'
Solution

  • Aaron Bertrand has an article about the differences but generally, FORMAT is more powerful in terms of the control of the output but more expensive as an operator.

    So, I use CONVERT if possible and FORMAT only for output which are not available with CONVERT.