Essa é uma das perguntas feitas com maior frequência no web. Converter uma data ou coluna, do tipo datetime, em um formato de data específica.
Abaixo segue um resumo dos formatos de data diferentes, que vêm como padrão no SQL Server utilizando a função CONVERT.
A saída dessas data são de tipos VARCHAR e já não de DATETIME. Sendo assim, qualquer comparação realizada após essa conversão deve ser com outro valor convertido também.
| Formatos de Data Padrão | |||
| Formato de Data | Padrão | Declaração SQL | Exemplo de Saída |
| Mon DD YYYY 1 | Default | SELECT CONVERT(VARCHAR(20), GETDATE(), 100) | Jan 1 2005 1:29PM 1 |
| HH:MIAM (or PM) | |||
| MM/DD/YY | USA | SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] | 11/23/98 |
| MM/DD/YYYY | USA | SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] | 11/23/1998 |
| YY.MM.DD | ANSI | SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] | 72.01.01 |
| YYYY.MM.DD | ANSI | SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] | 1972.01.01 |
| DD/MM/YY | British/French | SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] | 19/2/1972 |
| DD/MM/YYYY | British/French | SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] | 19/2/1972 |
| DD.MM.YY | German | SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] | 25.12.05 |
| DD.MM.YYYY | German | SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] | 25.12.2005 |
| DD-MM-YY | Italian | SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] | 24/1/1998 |
| DD-MM-YYYY | Italian | SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] | 24/1/1998 |
| DD Mon YY 1 | - | SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] | 04 Jul 06 1 |
| DD Mon YYYY 1 | - | SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] | 04 Jul 2006 1 |
| Mon DD, YY 1 | - | SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] | Jan 24, 98 1 |
| Mon DD, YYYY 1 | - | SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] | Jan 24, 1998 1 |
| HH:MM:SS | - | SELECT CONVERT(VARCHAR(8), GETDATE(), 108) | 03:24:53 |
| Mon DD YYYY HH:MI:SS:MMM AM (or PM) 1 | Default + | SELECT CONVERT(VARCHAR(26), GETDATE(), 109) | Apr 28 2006 12:32:29:253PM 1 |
| milliseconds | |||
| MM-DD-YY | USA | SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] | 1/1/2006 |
| MM-DD-YYYY | USA | SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] | 1/1/2006 |
| YY/MM/DD | - | SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] | 98/11/23 |
| YYYY/MM/DD | - | SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] | 23/11/1998 |
| YYMMDD | ISO | SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] | 980124 |
| YYYYMMDD | ISO | SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] | 19980124 |
| DD Mon YYYY HH:MM:SS:MMM(24h) 1 | Europe default + milliseconds | SELECT CONVERT(VARCHAR(24), GETDATE(), 113) | 28 Apr 2006 00:34:55:190 1 |
| HH:MI:SS:MMM(24H) | - | SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] | 11:34:23:013 |
| YYYY-MM-DD HH:MI:SS(24h) | ODBC Canonical | SELECT CONVERT(VARCHAR(19), GETDATE(), 120) | 1/1/1972 13:42 |
| YYYY-MM-DD HH:MI:SS.MMM(24h) | ODBC Canonical | SELECT CONVERT(VARCHAR(23), GETDATE(), 121) | 1972-02-19 06:35:24.489 |
| (with milliseconds) | |||
| YYYY-MM-DDTHH:MM:SS:MMM | ISO8601 | SELECT CONVERT(VARCHAR(23), GETDATE(), 126) | 1998-11-23T11:25:43:250 |
| DD Mon YYYY HH:MI:SS:MMMAM 1 | Kuwaiti | SELECT CONVERT(VARCHAR(26), GETDATE(), 130) | 28 Apr 2006 12:39:32:429AM 1 |
| DD/MM/YYYY HH:MI:SS:MMMAM | Kuwaiti | SELECT CONVERT(VARCHAR(25), GETDATE(), 131) | 28/04/2006 12:39:32:429AM |
| Formato de Data Extendido | ||
| Formato de Data | Declaração SQL | Exemplo de Saída |
| YY-MM-DD | SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] | 99-01-24 |
| SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD] | ||
| YYYY-MM-DD | SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] | 24/1/1999 |
| SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD] | ||
| MM/YY | SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] | ago/99 |
| SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY] | ||
| MM/YYYY | SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] | dez/05 |
| YY/MM | SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] | 99/08 |
| YYYY/MM | SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] | 2005/12 |
| Month DD, YYYY 1 | SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] | July 04, 20061 |
| Mon YYYY1 | SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] | Apr 2006 1 |
| Month YYYY 1 | SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] | February 2006 1 |
| DD Month1 | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month] | 11 September 1 |
| Month DD1 | SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] | September 11 1 |
| DD Month YY 1 | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] | 19 February 72 1 |
| DD Month YYYY 1 | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] | 11 September 2002 1 |
| MM-YY | SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] | dez/92 |
| SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY] | ||
| MM-YYYY | SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] | mai/06 |
| YY-MM | SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] | 92/12 |
| SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM] | ||
| YYYY-MM | SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] | 2006-05 |
| MMDDYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY] | 122506 |
| MMDDYYYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY] | 12252006 |
| DDMMYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY] | 240702 |
| DDMMYYYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY] | 24072002 |
| Mon-YY 1 | SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY] | Sep-02 1 |
| Mon-YYYY1 | SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY] | Sep-2002 1 |
| DD-Mon-YY 1 | SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY] | 25-Dec-05 1 |
| DD-Mon-YYYY 1 | SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY] | 25-Dec-20051 |
1 Para colocar o nome do mês em maiúsculas, basta utilizar a função UPPER.
Até a próxima!
Legal, não tem em lugar algum esta informação...
ResponderExcluir