Seguindo a filosofia de que pensar que: tempo = dinheiro, mulher gosta de dinheiro, quanto mais dinheiro mais mulher, quanto mais mulher melhor. Utilizando o sislogismo hipotético: quanto mais tempo, melhor o mundo. Resolvi fazer esta function que provavelmente será apenas uma vez na vida, para que as coisas melhorem no mundo ao se ganhar mais tempo.
Com o padrão de formatação do C# (conforme pode ser encontrado aqui http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx), peguei na net uma base e modifiquei quase totalmente para utilizar este padrão do C# (sorry, foram vários endereços e acabei não guardando). Utilizei padrão de linguagem em português e tem a maioria das codificações disponívels nesse link aí em cima.
Tem uma parte relacionada com microsegundos e nanosegundos que está comentada pois só funciona para SQL Server 2008. Para utilizar basta descomentar.
Se encontrar algum bug, código pronto com melhoria, ou coisas do tipo para me enviar. Basta comentar que eu estarei gerando uma versão mais atual.
Espero que isso ajude a quem usar.
CREATE FUNCTION [dbo].[FN_FORMATA_DATE](
@Datetime DATETIME,
@FormatMask VARCHAR(64)
)
RETURNS VARCHAR(64)
AS
BEGIN
DECLARE
@StringDate VARCHAR(64)
SET @StringDate = @FormatMask
/*
Function v1.0
"d" The day of the month, from 1 through 31. 6/1/2009 1:45:30 PM -> 1
"dd" The day of the month, from 01 through 31. 6/1/2009 1:45:30 PM -> 01
"ddd" The abbreviated name of the day of the week. 6/15/2009 1:45:30 PM -> Seg
"dddd" The full name of the day of the week. 6/15/2009 1:45:30 PM -> Segunda-Feira
"n" The milisecond in a date and time value. 6/15/2009 13:45:30.617 -> 123
--Sql Server 2008
"nn" The microsecond in a date and time value. 6/15/2009 13:45:30.617 -> 123456
"nnn" The nanosecond in a date and time value. 6/15/2009 13:45:30.617 -> 123456700
"h" The hour, using a 12-hour clock from 1 to 12. 6/15/2009 1:45:30 AM -> 1
"hh" The hour, using a 12-hour clock from 01 to 12. 6/15/2009 1:45:30 AM -> 01
"H" The hour, using a 24-hour clock from 0 to 23. 6/15/2009 1:45:30 AM -> 1
"HH" The hour, using a 24-hour clock from 00 to 23. 6/15/2009 1:45:30 AM -> 01
--não implementado
"K" (Não implementado) Time zone information. With DateTime values:
More information: The "K" Custom Format Specifier. 6/15/2009 1:45:30 PM, Kind Unspecified ->
6/15/2009 1:45:30 PM, Kind Utc -> Z
6/15/2009 1:45:30 PM, Kind Local -> -07:00 (depends on local computer settings)
With DateTimeOffset values:
6/15/2009 1:45:30 AM -07:00 --> -07:00
6/15/2009 8:45:30 AM +00:00 --> +00:00
"m" The minute, from 0 through 59. 6/15/2009 1:09:30 AM -> 9
"mm" The minute, from 00 through 59. 6/15/2009 1:09:30 AM -> 09
"M" The month, from 1 through 12. 6/15/2009 1:45:30 PM -> 6
"MM" The month, from 01 through 12. 6/15/2009 1:45:30 PM -> 06
"MMM" The abbreviated name of the month. 6/15/2009 1:45:30 PM -> Jun
"MMMM" The full name of the month. 6/15/2009 1:45:30 PM -> Junho
"s" The second, from 0 through 59. 6/15/2009 1:45:09 PM -> 9
"ss" The second, from 00 through 59. 6/15/2009 1:45:09 PM -> 09
"ampm" The AM/PM designator lowercase. 6/15/2009 1:45:30 PM -> pm
"AMPM" The AM/PM designator uppercase. 6/15/2009 1:45:30 PM -> PM
"yy" The year, from 00 to 99. 1/1/0001 12:00:00 AM -> 01
"yyyy" The year as a four-digit number. 1/1/0001 12:00:00 AM -> 0001
--não implementado
"z" Hours offset from UTC, with no leading zeros. 6/15/2009 1:45:30 PM -07:00 -> -7
"zz" Hours offset from UTC, with a leading zero for a single-digit value. 6/15/2009 1:45:30 PM -07:00 -> -07
"zzz" Hours and minutes offset from UTC. 6/15/2009 1:45:30 PM -07:00 -> -07:00
*/
IF (CHARINDEX ('MMMM', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS, 'MMMM', '|Q|')
IF (CHARINDEX ('MMM', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS, 'MMM', '|E|')
IF (CHARINDEX ('AMPM', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
BEGIN
SET @StringDate = REPLACE(@StringDate, 'AMPM' COLLATE SQL_Latin1_General_CP1_CS_AS, '|X|')
END
IF (CHARINDEX ('ampm', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
BEGIN
SET @StringDate = REPLACE(@StringDate, 'ampm' COLLATE SQL_Latin1_General_CP1_CS_AS, '|x|')
END
IF (CHARINDEX ('YYYY', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'YYYY', DATENAME(YY, @Datetime))
IF (CHARINDEX ('YY', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'YY', RIGHT(DATENAME(YY, @Datetime), 2))
IF (CHARINDEX ('MM', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate, 'MM' COLLATE SQL_Latin1_General_CP1_CS_AS, RIGHT('0' + CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX ('M', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate, 'M' COLLATE SQL_Latin1_General_CP1_CS_AS, CONVERT(VARCHAR, DATEPART(MM, @Datetime)))
IF (CHARINDEX ('DDDD', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'DDDD', '|O|')
IF (CHARINDEX ('DDD', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'DDD', '|P|')
IF (CHARINDEX ('DD', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'DD', RIGHT('0' + DATENAME(DD, @Datetime),2))
IF (CHARINDEX ('D', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'D', DATENAME(DD, @Datetime))
IF (CHARINDEX ('h', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
BEGIN
DECLARE @Hour INT; SET @Hour = DATEPART(HH, @Datetime)
SELECT @Hour = CASE WHEN @Hour >= 12 THEN @Hour - 12 ELSE @Hour END
IF (CHARINDEX ('hh', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'hh' COLLATE SQL_Latin1_General_CP1_CS_AS, RIGHT('0' + CONVERT(VARCHAR, @Hour), 2))
IF (CHARINDEX ('h', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'h' COLLATE SQL_Latin1_General_CP1_CS_AS, @Hour)
END
IF (CHARINDEX ('HH', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate, 'HH' COLLATE SQL_Latin1_General_CP1_CS_AS, RIGHT('0' + CONVERT(VARCHAR, DATEPART(HOUR, @Datetime)), 2))
IF (CHARINDEX ('H', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate, 'H' COLLATE SQL_Latin1_General_CP1_CS_AS, CONVERT(VARCHAR, DATEPART(HOUR, @Datetime)))
IF (CHARINDEX ('mm', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'mm', RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, @Datetime)), 2))
IF (CHARINDEX ('m', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'm', DATEPART(mi, @Datetime))
IF (CHARINDEX ('ss', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'ss', RIGHT('0'+CONVERT(VARCHAR,DATEPART(ss, @Datetime)),2))
IF (CHARINDEX ('s', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 's', DATEPART(ss, @Datetime))
-- IF (CHARINDEX ('nnn', @StringDate) > 0)
-- SET @StringDate = REPLACE(@StringDate, 'nnn', DATEPART(nanosecond, @Datetime))
-- IF (CHARINDEX ('nn', @StringDate) > 0)
-- SET @StringDate = REPLACE(@StringDate, 'nn', DATEPART(microsecond, @Datetime))
IF (CHARINDEX ('n', @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, 'n', DATEPART(millisecond, @Datetime))
IF (CHARINDEX ('|Q|', @StringDate) > 0)
SET @StringDate = CASE DATEPART(MONTH, @Datetime)
WHEN 01 THEN (REPLACE(@StringDate, '|Q|', 'Janeiro'))
WHEN 02 THEN (REPLACE(@StringDate, '|Q|', 'Fevereiro'))
WHEN 03 THEN (REPLACE(@StringDate, '|Q|', 'Março'))
WHEN 04 THEN (REPLACE(@StringDate, '|Q|', 'Abril'))
WHEN 05 THEN (REPLACE(@StringDate, '|Q|', 'Maio'))
WHEN 06 THEN (REPLACE(@StringDate, '|Q|', 'Junho'))
WHEN 07 THEN (REPLACE(@StringDate, '|Q|', 'Julho'))
WHEN 08 THEN (REPLACE(@StringDate, '|Q|', 'Agosto'))
WHEN 09 THEN (REPLACE(@StringDate, '|Q|', 'Setembro'))
WHEN 10 THEN (REPLACE(@StringDate, '|Q|', 'Outubro'))
WHEN 11 THEN (REPLACE(@StringDate, '|Q|', 'Novembro'))
WHEN 12 THEN (REPLACE(@StringDate, '|Q|', 'Dezembro'))
END
IF (CHARINDEX ('|E|', @StringDate) > 0)
SET @StringDate = CASE DATEPART(MONTH, @Datetime)
WHEN 01 THEN (REPLACE(@StringDate, '|E|', 'Jan'))
WHEN 02 THEN (REPLACE(@StringDate, '|E|', 'Fev'))
WHEN 03 THEN (REPLACE(@StringDate, '|E|', 'Mar'))
WHEN 04 THEN (REPLACE(@StringDate, '|E|', 'Abr'))
WHEN 05 THEN (REPLACE(@StringDate, '|E|', 'Mai'))
WHEN 06 THEN (REPLACE(@StringDate, '|E|', 'Jun'))
WHEN 07 THEN (REPLACE(@StringDate, '|E|', 'Jul'))
WHEN 08 THEN (REPLACE(@StringDate, '|E|', 'Ago'))
WHEN 09 THEN (REPLACE(@StringDate, '|E|', 'Set'))
WHEN 10 THEN (REPLACE(@StringDate, '|E|', 'Out'))
WHEN 11 THEN (REPLACE(@StringDate, '|E|', 'Nov'))
WHEN 12 THEN (REPLACE(@StringDate, '|E|', 'Dez'))
END
IF (CHARINDEX ('|X|', @StringDate) > 0)
BEGIN
DECLARE @AMPM VARCHAR(2)
IF DATEPART(HH, @Datetime) > 12
SET @AMPM = 'pm'
ELSE
SET @AMPM = 'am'
IF CHARINDEX ('|X|', @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0
SET @AMPM = UPPER(@AMPM)
SET @StringDate = REPLACE(@StringDate, '|X|', @AMPM)
END
IF (CHARINDEX ('|O|', @StringDate) > 0)
SET @StringDate = CASE DATEPART(dw, @Datetime)
WHEN 01 THEN (REPLACE(@StringDate, '|O|', 'Domingo'))
WHEN 02 THEN (REPLACE(@StringDate, '|O|', 'Segunda-Feira'))
WHEN 03 THEN (REPLACE(@StringDate, '|O|', 'Terça-Feira'))
WHEN 04 THEN (REPLACE(@StringDate, '|O|', 'Quarta-Feira'))
WHEN 05 THEN (REPLACE(@StringDate, '|O|', 'Quinta-Feira'))
WHEN 06 THEN (REPLACE(@StringDate, '|O|', 'Sexta-Feira'))
WHEN 07 THEN (REPLACE(@StringDate, '|O|', 'Sábado'))
END
IF (CHARINDEX ('|P|', @StringDate) > 0)
SET @StringDate = CASE DATEPART(dw, @Datetime)
WHEN 01 THEN (REPLACE(@StringDate, '|P|', 'Dom'))
WHEN 02 THEN (REPLACE(@StringDate, '|P|', 'Seg'))
WHEN 03 THEN (REPLACE(@StringDate, '|P|', 'Ter'))
WHEN 04 THEN (REPLACE(@StringDate, '|P|', 'Qua'))
WHEN 05 THEN (REPLACE(@StringDate, '|P|', 'Qui'))
WHEN 06 THEN (REPLACE(@StringDate, '|P|', 'Sex'))
WHEN 07 THEN (REPLACE(@StringDate, '|P|', 'Sab'))
END
RETURN @StringDate
END
GO