Efficiently Rounding a SQL Server datetime

Truncates hours, minutes and seconds without messy string manipulation:

CAST(FLOOR(CAST(GETDATE() AS DECIMAL(8,2))) AS datetime)

Underdocumented SQL Server mail parameter

Due to popular demand, Microsoft added the @query_result_no_padding to the sp_send_dbmail stored procedure.

Sadly, you wont find this in the online help. It was added as part of a Service Pack.

Use this parameter to trim excess spaces from the query results attached to the email. Otherwise, the column output pads out to the length of the char OR varchar field.

For example:
declare @t TABLE (field varchar(10))

insert into @t
select 'hello'

When sent with @query_result_no_padding = 1
the result is:

field
-----
hello

otherwise

field
----------
hello

Decode HTML Strings in SQL Server

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- EXEC UrlDecode '%c3%a1'
-- EXEC UrlDecode '\\Tul1ciprmw1\RichMediaAssets\Open\Telemundo\Al Rojo Vivo con Mar%c3%ada Celeste\_derived_jpg_q90_172x128_m1\3ARValx05Mar%c3%ada Celeste Arrar%c3%a1s.jpg'
-- EXEC UrlDecode '%4D'

ALTER PROCEDURE UrlDecode
-- Add the parameters for the stored procedure here
@URL varchar(2000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @Position INT,
@Base CHAR(16),
@High TINYINT,
@Low TINYINT,
@NewHigh INT,
@Pattern CHAR(21)

SELECT @Base = '0123456789abcdef',
@Pattern = '%[%][0-9a-f][0-9a-f]%',
@Position = PATINDEX(@Pattern, @URL)

SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base) -1,
@Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base) -1

WHILE @Position > 0
BEGIN

IF (@High & 15) = 12 -- xC0
BEGIN
SELECT @NewHigh = @Low * POWER(2, 6) --Shift Low 6 bits

SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 4, 1), @Base) -1
SELECT @Low = CHARINDEX(SUBSTRING(@URL, @Position + 5, 1), @Base) -1

SELECT @URL = STUFF(@URL, @Position, 6, CHAR(@NewHigh | (16 * @High) | @Low))

SELECT @High = 0, @Low = 0, @NewHigh = 0

END
ELSE
BEGIN
SELECT @URL = STUFF(@URL, @Position, 3, CHAR(16 * @High | @Low))

END

SELECT @Position = PATINDEX(@Pattern, @URL)
IF @Position > 0
BEGIN
SELECT @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base) -1,
@Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base) -1
END
END

SELECT @URL

END
GO