Home > SQL Server > SQL SERVER – ISNumeric(), ISDate(), ISNULL() Functions @SQLServer

SQL SERVER – ISNumeric(), ISDate(), ISNULL() Functions @SQLServer


Data validation is an important task for developers to make sure that they are getting correct data for processing. This is per-validation step to avoid unnecessary error from the system.

SQL Server has few TSQL functions that help us to check for some data type and null value.

Functions:

ISNUMERIC()

It checks whether a given data is numeric or not. It returns 1 (True) if the given value is numeric or 0 (False) if it is not numeric. it evaluates numeric data with or without sign such as + or – and it accepts currency symbols with numeric data also known as money.

Experiment

SELECT ISNUMERIC(’12’) AS Result
SELECT ISNUMERIC(‘+12′) AS Result
SELECT ISNUMERIC(-12) AS Result

SELECT ISNUMERIC(’12a’) AS Result

–Data with Currency Symbol
SELECT ISNUMERIC($12) AS Result –US Dollar
SELECT ISNUMERIC(‘€12’) AS Result –Euro
SELECT ISNUMERIC(‘¥12’) AS Result –Yen
SELECT ISNUMERIC(‘£12’) AS Result –Pound
–NULL value
SELECT ISNUMERIC(NULL) AS Result

ISDATE()

It checks whether a given data is date or not. It returns 1 (True) if the value is Date or 0 (False) if the value is not a Date.

The argument data type of this function must be date, time or datetime. Otherwise, this function returns (0) False.

We cannot pass an argument with scalar variable of type Date, Time, datetime2 and etc. except datetime scalar variable. Examples are given in experiment section.

Experiment

DECLARE @dDate AS Date
DECLARE @tTime AS TIME
DECLARE @dtDateTime2 AS datetime2
--Invalid scalar variable for the ISDate() argument
SELECT ISDATE(@dDate) AS Result
SELECT ISDATE(@tTime) AS Result
SELECT ISDATE(@dtDateTime2) AS Result

–Valid function example

–Date
SELECT ISDATE(‘1900-01-01’) AS Result
SELECT ISDATE(‘1900-01-01’) AS Result
SELECT ISDATE(‘9999-01-01’) AS Result
SELECT ISDATE(‘10000-01-01’) AS Result
SELECT ISDATE(‘0000-01-01’) AS Result
SELECT ISDATE(‘+1900-01-01′) AS Result
–Time
SELECT ISDATE(’12:00am’)  AS Result
SELECT ISDATE(’12:00′)  AS Result
SELECT ISDATE(’00:00:00′)  AS Result
SELECT ISDATE(’00:00:00.999′)  AS Result
SELECT ISDATE(’00:00:00.1000′)  AS Result
–DateTime
SELECT ISDATE(‘1753-01-01 12:00:00.3am’) AS Result
SELECT ISDATE(‘1752-12-31 12:00:00.3am’) AS Result
SELECT ISDATE(‘1600-01-01 12:00:00.3am’) AS Result
SELECT ISDATE(‘1900-01-0112:00:00.3am’) AS Result
SELECT ISDATE(’01-01-01′) AS Result
SELECT ISDATE(‘1752-01-01’) AS Result
–NULL value
SELECT ISDATE(NULL) AS Result

ISNULL (,)

It replaces or returns a given value instead NULL. It is a helpful function in presentation layer or in data cleaning.  we can use this function in select statement to get desire output without modifying the original data.

Experiment

SELECT ISNULL (NULL, ‘N/A’) AS Result
SELECT ISNULL (NULL, 9) AS Result
SELECT ISNULL (NULL, 0.00) AS Result

Execute this SQL Query in SQL Server 2008 R2 Management studio.

Thanks for reading.

Advertisements
  1. No comments yet.
  1. December 3, 2012 at 5:15 pm
  2. December 4, 2012 at 5:45 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: