SELECT SUBSTRING(CONVERT(varchar(24), GetDate(), 121), 12, 12)
and
SELECT DATEADD(Day, 0-DATEDIFF(Day, 0, GetDate()), GetDate())

Seconds Test
-------  ----------------------------
  1.453 SELECT COUNT(*) FROM MyTable

(Result = 12,689,651 rows)

  6.813 SELECT @TempDate = MyDate FROM MyTable

  6.623 SELECT @TempDate = DATEADD(day, DATEDIFF(day, 0, MyDate), 0) FROM MyTable

 37.203 SELECT @TempDate = CONVERT(varchar(8), MyDate, 101) FROM MyTable

-- Causes overflow
-- SELECT 'Second', DATEADD(Second, DATEDIFF(Second, 0, GetDate()), 0)
-- UNION ALL

SELECT 'Minute', DATEADD(Minute, DATEDIFF(Minute, 0, GetDate()), 0)
UNION ALL
SELECT 'Hour', DATEADD(Hour, DATEDIFF(Hour, 0, GetDate()), 0)
UNION ALL
SELECT 'Day', DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
UNION ALL
SELECT 'Week', DATEADD(Week, DATEDIFF(Week, 0, GetDate()), 0)
UNION ALL
SELECT 'Month', DATEADD(Month, DATEDIFF(Month, 0, GetDate()), 0)
UNION ALL
SELECT 'Quarter', DATEADD(Quarter, DATEDIFF(Quarter, 0, GetDate()), 0)
UNION ALL
SELECT 'Year', DATEADD(Year, DATEDIFF(Year, 0, GetDate()), 0)

-- Just time:
select convert(varchar(10), GetDate(), 108)

SET NOCOUNT ON

DECLARE
	@min INT,
	@max INT,
	@date DATETIME

SELECT
	@min = 1,
	@max = 131,
	@date = GETDATE()

SELECT @date

WHILE @min <= @max
BEGIN

	IF @min BETWEEN 15 AND 19 
		OR @min  = 26 
		OR @min BETWEEN 27 AND 99
		OR @min BETWEEN 115 AND 119
		OR @min BETWEEN 122 AND 125
		OR @min BETWEEN 127 AND 129
		BEGIN
			GOTO NEXT_LOOP
		END

	SELECT @min, CONVERT(VARCHAR,@date,@min)

NEXT_LOOP:

SELECT @min = @min + 1
END