SQL中的格式化和转换函数在选择语句中的使用

huangapple go评论46阅读模式
英文:

SQL Format and Convert functions in a select statement

问题

1: FORMAT(pb.FINICIO, 'dd/MM/yyyy') as finicio 这是一种使用FORMAT函数将日期格式化为DD/MM/YYYY格式的方法。

2: CONVERT(VARCHAR(10), pb.FFIN, 103) AS [DD/MM/YYYY] 这是一种使用CONVERT函数将日期格式化为DD/MM/YYYY格式的方法。

完整查询不翻译。

英文:

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

得分: 2

Aaron Bertrand在1文章中详细介绍了它们之间的差异,但一般来说,FORMAT在输出控制方面更强大,但作为运算符更昂贵。

所以,如果可能的话,我会使用CONVERT,只有在使用CONVERT不可用时才使用FORMAT

英文:

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.

huangapple
  • 本文由 发表于 2023年4月11日 14:15:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/75982882.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定