Bruno Perroni
DBA SQL Server com mais de 6 anos de experiência!

Datetime Formats SQL Server

Fala galera, blz ?

Nesse post vou mostrar diversos formatos de datetime para você utilizar no dia-a-dia.

Certamente alguns deles serão úteis, para a realização de conversões de datas entre formatos diferentes.

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)	AS [Mmm DD YYYY HH:DD(AM/PM)]
--Nov 21 2018 10:24PM

SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]	
--11/21/18

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]	
--11/21/2018

SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]	
--18.11.21

SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]
--2018.11.21

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
--21/11/18

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
--21/11/2018

SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
--21.11.18

SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
--21.11.2018

SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]
--21-11-18

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
--21-11-2018

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD Mmm YY]
--21 Nov 18

SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD Mmm YYYY]
--21 Nov 2018

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mmm DD, YY]
--Nov 21, 18

SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mmm DD, YYYY]
--Nov 21, 2018

SELECT CONVERT(VARCHAR(8), GETDATE(), 108) AS [HH:MM:SS]
--22:33:10

SELECT CONVERT(VARCHAR(26), GETDATE(), 109) AS [Mmm DD YYYY HH:MM:SS.ffff(AM/PM)]
--Nov 21 2018 10:33:42:700PM

SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
--11-21-18

SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
--11-21-2018

SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]
--18/11/21

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
--2018/11/21

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]
--181121

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]
--20181121

SELECT CONVERT(VARCHAR(24), GETDATE(), 113) AS [DD Mmm YYYY HH:MM:SS.fff]
--21 Nov 2018 22:38:09:653

SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MM:SS:fff(24H)]
--23:06:20:267

SELECT CONVERT(VARCHAR(19), GETDATE(), 120) AS [YYYY-MM-DD HH:MM:SS]
--2018-11-21 22:39:55

SELECT CONVERT(VARCHAR(23), GETDATE(), 121)  AS [YYYY-MM-DD HH:MM:SS.fff]
--2018-11-21 22:40:30.637

SELECT CONVERT(VARCHAR(23), GETDATE(), 126) AS [YYYY-MM-DDTHH:MM:SS.fff]
--2018-11-21T22:42:31.980

SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
--18-11-21

SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]
--18-11-21

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
--2018-11-21

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]
--2018-11-21

SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]
--11/18

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]
--11/18

SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]
--11/2018

SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]
--18/11

SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]
--2018/11

SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Mês DD, YYYY]
--November 21, 2018

SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mmm YYYY]
--Nov 2018

SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Mês YYYY]
--November 2018

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Mês]
--21 November

SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Mês DD]
--November 21

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Mês YY]
--21 November 18

SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Mês YYYY]
--21 November 2018

SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]
--11-18

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]
--11-18

SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]
--11-2018

SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]
--18-11

SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]
--18-11

SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]
--2018-11

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]
--112118

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]
--11212018

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]
--211118

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]
--21112018

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mmm-YY]
--Nov-18

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mmm-YYYY]
--Nov-2018

SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mmm-YY]
--21-Nov-18

SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mmm-YYYY]
--21-Nov-2018