Aquí os dejo varias formas de formatear fechas en SQL.
Mas formatos de Fechas que no vienen con el standard de SQL, para ello usaremos la función CONVERT function.
Formatos de Fechas Standard | |||
Formato Fecha | Standard | Sentencia SQL | Resultado |
Mon DD YYYY HH:MIAM (or PM) |
Default | SELECT CONVERT(VARCHAR(20), GETDATE(), 100) | Jan 1 2005 1:29PM |
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/02/72 |
DD/MM/YYYY | British/French | SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] | 19/02/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-01-98 |
DD-MM-YYYY | Italian | SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] | 24-01-1998 |
DD Mon YY | - | SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] | 04 Jul 06 |
DD Mon YYYY | - | SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] | 04 Jul 2006 |
Mon DD, YY | - | SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] | Jan 24, 98 |
Mon DD, YYYY | - | SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] | Jan 24, 1998 |
HH:MM:SS | - | SELECT CONVERT(VARCHAR(8), GETDATE(), 108) | 03:24:53 |
Mon DD YYYY HH:MI:SS:MMMAM (or PM) | Default +
milliseconds |
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) | Apr 28 2006 12:32:29:253PM |
MM-DD-YY | USA | SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] | 01-01-06 |
MM-DD-YYYY | USA | SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] | 01-01-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] | 1998/11/23 |
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) | Europe default + milliseconds | SELECT CONVERT(VARCHAR(24), GETDATE(), 113) | 28 Apr 2006 00:34:55:190 |
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) | 1972-01-01 13:42:24 |
YYYY-MM-DD HH:MI:SS.MMM(24h) | ODBC Canonical (with milliseconds) |
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) | 1972-02-19 06:35:24.489 |
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 | Kuwaiti | SELECT CONVERT(VARCHAR(26), GETDATE(), 130) | 28 Apr 2006 12:39:32:429AM |
DD/MM/YYYY HH:MI:SS:MMMAM | Kuwaiti | SELECT CONVERT(VARCHAR(25), GETDATE(), 131) | 28/04/2006 12:39:32:429AM |
Formato extendido de Fechas | ||
Formato Fecha | Sentencia SQL | Resultado |
YY-MM-DD |
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]
|
99-01-24 |
YYYY-MM-DD |
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]
|
1999-01-24 |
MM/YY | SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY] |
08/99 |
MM/YYYY | SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] | 12/2005 |
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 | SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] | July 04, 2006 |
Mon YYYY | SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] | Apr 2006 |
Month YYYY | SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] | February 2006 |
DD Month | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month] | 11 September |
Month DD | SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] | September 11 |
DD Month YY | 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 |
DD Month YYYY | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] | 11 September 2002 |
MM-YY | SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY] |
12/92 |
MM-YYYY | SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] | 05-2006 |
YY-MM | SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM] |
92/12 |
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 | SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY] | Sep-02 |
Mon-YYYY | SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY] | Sep-2002 |
DD-Mon-YY | SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY] | 25-Dec-05 |
DD-Mon-YYYY | SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY] | 25-Dec-2005 |
0 comentarios:
Publicar un comentario