Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Determine if a Table has a Primary Key
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

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