How to check if the input is numeric

ISNUMERIC() – This is a system function which can be used to evaluate the given input, if the input is valid numeric it returns 1 otherwise 0. 

There are times where we need to check if the input value contains any non numeric chars. the ISNUMERIC() can not be used for this purpose as it evaluates numbers, money and decimal to true.

Example:
1)  Input value contains only numbers

DECLARE @input VARCHAR(10)='102030'
SELECT ISNUMERIC(@input) IsNumber

2)  Input value is decimal

DECLARE @input VARCHAR(10)='102030.40'
SELECT ISNUMERIC(@input) IsNumber

3)  Input value is money

DECLARE @input VARCHAR(10)='$102030'
SELECT ISNUMERIC(@input) IsNumber

PATINDEX to check existence of non numeric chars

The PATINDEX function can be used to check if any non numeric char exists in the input.

DECLARE @input VARCHAR(10)='102030.40'
SELECT PATINDEX('%[^0-9]%',RTRIM(LTRIM(@input))) AS IsNumber

This statement returns the position of any chars other than numbers. if the statement returns greater than 0 then the input is not a valid number.

Read More

How to find when was SQL Server restarted?

There are few ways to find out when the SQL Server was restarted, here I am going to use DMV (sys.dm_os_sys_info) to find the SQL Server restarted time.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Read More

Usage of @@Error within transaction

@@ERROR returns error number of the last statement executed. When the last executed statement completed successfully, this returns 0. In case of any error, the error number will be return.

Example:

Declare @n int, @d int
Set @n=10
Set @d=0
Select @n/@d
PRINT @@ERROR


I am going to show you a common mistake most of the developer does when using @@ERROR and PRINT, In fact I did so, that’s why I am able write this blog.

Let’s take a look at an example


Create table tblPrintErr(rowId tinyint)
Begin Tran
Insert into tblPrintErr(rowId) values (1)
PRINT @@ERROR
Insert into tblPrintErr(rowId) values (300) // This statement will generate an error as 300 is not a tinyint
PRINT @@ERROR
IF @@ERROR = 0
BEGIN
    COMMIT TRAN
END
ELSE
BEGIN
    ROLLBACK TRAN
END


I am forcefully creating an error in order to roll back the transaction, but what really happened was the transaction got committed.
Let’s see what caused for the transaction to commit instead of roll back.

PRINT @@ERROR statement next to the insert query is what caused the transaction to commit. When IF @@ERROR = 0 statement executed, the last executed statement was PRINT @@ERROR, since this statement executed without error, @@ERROR holds value of 0 and transaction went into commit.

You need to be careful, when you use @@ERROR. In order to avoid the above discussed behaviour. Use local variable to hold the value of @@ERROR.

Example:


Declare @Errno int
Insert into tblPrintErr(rowId) values (300)
Set @Errno = @@ERROR
.
.
.
.
IF @Errno =0
BEGIN
    … … … … 
END
ELSE
BEGIN
    … … … … 
END


CodeProject

Read More

Be aware when you use date to filter the records

 When you execute the below query, as you expect, the result will be “September 26^th 2049”

DECLARE @DATE AS DATETIME
SET @DATE = '09/26/49'
SELECT @DATE

Now, Lets see what happen when we pass 50 instead of 49

DECLARE @DATE AS DATETIME
SET @DATE = '09/26/50'
SELECT @DATE

You will get the result as “September 26^th 1950”; this is because by default, SQL Server interprets two-digit years based on a cutoff year of 2049.

DECLARE @DATE AS DATETIME
SET @DATE = '09/26/2050'
SELECT @DATE

Now, as you expect you will get the result as “September 26^th 2050”; so it is always good to specify the four-digit year.

Notes from BOL

By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years.”

Read More

How to find the Date is first day of the Week in SQL.

Some time you asked to find the given date is first day of the week or not? here is the code.

IF DATEPART(DW,GETDATE()) = @@DATEFIRST
BEGIN
PRINT CONVERT(VARCHAR,GETDATE(),110) + ' IS FIRST DAY OF THE WEEK'
END
ELSE
BEGIN
PRINT CONVERT(VARCHAR,GETDATE(),110) + ' IS NOT A FIRST DAY OF THE WEEK'
END

Read More

How to find whether the date is end date of the month in SQL

To know the given date is end date of the month, You can use the dateadd and day functions. that is, you should add 1 day to the given date and check whether the result date's day is 1 or not. If the result date's day is one then the given date is end date of the month otherwise it is not the end date of the month.

SELECT CASE WHEN DAY(DATEADD(D,1,GETDATE()))=1 THEN 'MONTH END' ELSE 'NOT MONTH END' END AS PERIOD

Read More