Tip of the Day : SQL Server Tutorial - INSERT Statement 101

SQL Server Helper - Tip of the Day

Example Uses of the FLOOR Mathematical Function

The FLOOR mathematical function returns the largest integer less than or equal to the specified numeric expression.  The syntax of the FLOOR mathematical function is as follows:

FLOOR ( < numeric_expression > )

The < numeric_expression > parameter is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.  The data type returned by the FLOOR mathematical function is the same data type as the parameter passed to it.

Usage #1 : Determine if a Float or Decimal is an Integer

DECLARE @Input		FLOAT

SET @Input = 123.456

IF @Input = FLOOR(@Input)
    PRINT '@Input is an Integer'
ELSE
    PRINT '@Input is NOT an Integer'
	
SET @Input = 1234.000
IF @Input = FLOOR(@Input)
    PRINT '@Input is an Integer'
ELSE
    PRINT '@Input is NOT an Integer'
GO

Usage #2 : Get the Decimal Part of a Float or Decimal Number

DECLARE @Input		DECIMAL(10, 4)

SET @Input = 12345.6789

SELECT @Input - FLOOR(@Input) AS [DecimalPart]
GO

Usage #3 : Get the Date Part of a Date/Time Value

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

Usage #4 : Get the Time Part of a Date/Time Value

SELECT GETDATE(), CAST(CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
Back to Tip of the Day List Next Tip