SQL Format and Convert functions in a select statement

74 Views Asked by At

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'
1

There are 1 best solutions below

0
gotqn On BEST ANSWER

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.