英文:
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:
-
What are the main differences between using a |FORMAT
or a
CONVERT` in a select statement. -
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
。
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论