ISDATE() revisited

ISDATE is a wonderful function used to test if the underlying value is a valid date. I have often found developers wink-their-eyes in shock over ISDATE’s non-deterministic behaviour. I hope this small presentation will help developers use ISDATE with care.

What this function does?

ISDATE function returns 1 if the value/expression under test is a valid datetime and returns 0 for all other values.

Syntax

ISDATE (value/expression under test)

Let us know take a deep-dive into application of this function. Though it would be very easy for me to list down what is valid (when function returns 1) and what is invalid (when function returns 0) date; it would be better to apply and check how this function behaves in different scenarios (build over different application sets, given below, with varied complexity). These will build-up our learning base very well.

Application Set – 1

Below is the list of queries where ISDATE is applied to different expressions. For all the these cases, consider that dateformat is set to DMY and language is set to English (US).

SELECT ISDATE('15/01/2012') -- Result returned = 1
SELECT ISDATE('15-01-2012') -- Result returned = 1
SELECT ISDATE('15-2012-01') -- Result returned = 1
SELECT ISDATE('01-15-2012') -- Result returned = 0
SELECT ISDATE('01-2012-15') -- Result returned = 0
SELECT ISDATE('2012-01-15') -- Result returned = 0
SELECT ISDATE('2012-15-01') -- Result returned = 1

As per ISDATE logic it is clear that for #1 and #2 the return value should be 1 and for all other it should be 0. But this is not the case!

#3 and #7 also have return value of 1. Why??

Because, SQL Server can implicitly convert expression (character string specifically) in #3 and #7 into valid datetime and for others (#4, #5 and #6) implicit conversion will fail with out-of-range conversion error. To demonstrate this, just try to execute the following code snippet in SSMS.

PRINT '@DT1'
DECLARE @DT1 DATETIME = '15/01/2012';

PRINT '@DT2'
DECLARE @DT2 DATETIME = '15-01-2012';

PRINT '@DT3'
DECLARE @DT3 DATETIME = '15-2012-01';

PRINT '@DT4'
DECLARE @DT4 DATETIME = '01-15-2012';

PRINT '@DT5'
DECLARE @DT5 DATETIME = '01-2012-15';

PRINT '@DT6'
DECLARE @DT6 DATETIME = '2012-01-15';

PRINT '@DT7'
DECLARE @DT7 DATETIME = '2012-15-01';

You will find the following error message for @DT4, @DT5 and @DT6
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Application Set – 2

Let us try to re-execute the same set of statements executed in Application Set – 1, but this time dateformat is set to MDY (SET DATEFORMAT MDY)

SELECT ISDATE('15/01/2012') -- Result returned = 0
SELECT ISDATE('15-01-2012') -- Result returned = 0
SELECT ISDATE('15-2012-01') -- Result returned = 0
SELECT ISDATE('01-15-2012') -- Result returned = 1
SELECT ISDATE('01-2012-15') -- Result returned = 1
SELECT ISDATE('2012-01-15') -- Result returned = 1
SELECT ISDATE('2012-15-01') -- Result returned = 0

Return value of the function is just the opposite. This does not need any explanation. But it is worth mentioning here that

response of ISDATE function depends upon the current dateformat

Application Set – 3

Dateformat being MDY let us change the language setting to something other than English (US) and check the result. Say for example, we are changing the language to Italian (SET LANGUAGE Italian)

SELECT ISDATE('15/01/2012') -- Result returned = 1
SELECT ISDATE('15-01-2012') -- Result returned = 1
SELECT ISDATE('15-2012-01') -- Result returned = 1
SELECT ISDATE('01-15-2012') -- Result returned = 0
SELECT ISDATE('01-2012-15') -- Result returned = 0
SELECT ISDATE('2012-01-15') -- Result returned = 0
SELECT ISDATE('2012-15-01') -- Result returned = 1

Again we noticed that this has an impact on ISDATE response. Hence it is worth mentioning that

response of ISDATE function depends upon current language settings

Application Set – 4

Let us try to check the behaviour of ISDATE when expression is of the following types (dateformat being DMY and language being English (US)):

  • Varchar
  • N Varchar
  • Datetime
  • Datetime2
  • Date
  • Null expression

Here is the code snippet used to check this:

SET DATEFORMAT DMY;
SET LANGUAGE English;
-- When expression => VARCHAR
DECLARE @dt_varchar VARCHAR(10) = '15-01-2012'
SELECT ISDATE(@dt_varchar)
GO
-- When expression => N'VARCHAR
DECLARE @dt_NVarchar NVARCHAR(10) = N'15-01-2012'
SELECT ISDATE(@dt_NVarchar)
GO
-- When expression => NULL
DECLARE @dt_Null DATETIME = NULL
SELECT ISDATE(@dt_Null)
GO
-- When expression => DATETIME
DECLARE @dt_DateTime DATETIME = '15-01-2012 00:00:00.000'
SELECT ISDATE(@dt_DateTime)
GO
-- When expression => DATETIME2
DECLARE @dt_DateTime2 DATETIME2 = '15-01-2012 00:00:00.0000000'
SELECT ISDATE(@dt_DateTime2)
GO
-- When expression => DATE
DECLARE @dt_Date DATE = '15-01-2012'
SELECT ISDATE(@dt_Date)
GO

Here is the resultant

#
Expression
Return Value
1
VARCHAR
1
2
N'VARCHAR
1
3
Is Null
0
4
DATETIME
1
5
DATETIME2
ERROR: Invalid argument
6
DATE
ERROR: Invalid argument

From above, picture about the behaviour of ISDATE is very clear now. It is also crystal clear that Datatime2 and Date data type are not valid arguments for ISDATE function. But care should be taken when datetime2 value is directly passed as character string in ISDATE argument. Here return value will be zero rather than an error message.

SELECT ISDATE('15-01-2012 00:00:00.0000000')
-- Returns 0

In nutshell, we can summarize our discussion with following points:

  • Behaviour of ISDATE function is dependent on default dateformat and language
  • It returns 1 when the string argument can implicitly be converted into valid datetime value. Increasing the seconds precision of the same string value to represent datetime2 format (or even if the seconds precision exceeds 3 significant digits) will return a 0 value.
  • Date and Datetime2 arguments are not allowed, they result in invalid argument error
  • Null values inside expressions also results in 0 as return value
  • Values such as text, ntext and image data types also return 0 value

I hope you enjoyed reading this post and must have admitted few facts to your memory.

The motivation to write this post came from SQLServerCentral Question of the Day. Here is the link to the question posted by me –> link

Happy Learning
Lokesh Vij

Advertisements

How to acquire a table lock for specific time?

Recently one of my colleagues asked me how to acquire an exclusive lock on a database table for a specific amount of time. And this lock should be released once the specified time expires.

You need two components to accomplish this task:
1) Table hints – that puts exclusive lock on the table
2) Delay element – that will hold the lock for a specified amount of time

1) Table hints
Table hint TABLOCKX can be used to acquire exclusive lock on the table.

 
SELECT *
FROM <table_name> 
WITH (TABLOCKX)

2) Delay element
WAITFOR statement inside transaction block can be used to introduce delay. Following sample code snippet introduces a delay for 10 seconds

 
WAITFOR DELAY '00:00:10' 

Here is the code snippet that creates a test table, inserts some data and places an exclusive lock for 10 seconds on this table

 
CREATE TABLE test_table
  (
     col1 INT
  ) 
GO 

INSERT test_table
VALUES (1)
GO

DECLARE @delay VARCHAR(8) = '00:00:10'

BEGIN TRAN

SELECT *
FROM   test_table WITH (TABLOCKX)
WHERE  col1 = '1'

WAITFOR DELAY @delay

ROLLBACK TRAN

Hope you enjoyed reading this post.

Happy Learning
Lokesh Vij

LIST Partitioning in SQL Server

SQL Server offers only range partitioning our tables, but what if I want to partition my table on the basis of List of values? Is it not doable?

The answer is Yes and No both – Yes because SQL Server offers only range based partitioning and No because, there is a trick to partition the table indirectly.

Let us not talk much and directly get into action to see how list partition works.

Below code snippet creates a simple table that we will like to partition basis list of values.

CREATE TABLE list_partition
(
id INT IDENTITY ( 1 , 1 ),
value CHAR ( 1 ) NOT NULL
)

INSERT INTO list_partition
( value )
VALUES ( 'A' ),
( 'B' ),
( 'C' ),
( 'D' ),
( 'A' ),
( 'B' ),
( 'A' ),
( 'E' ),
( 'F' ),
( 'G' )

SELECT *
FROM list_partition
| ID | VALUE |
--------------
|  1 |     A |
|  2 |     B |
|  3 |     C |
|  4 |     D |
|  5 |     A |
|  6 |     B |
|  7 |     A |
|  8 |     E |
|  9 |     F |
| 10 |     G |

Ideally I want to assign value ‘A’ and ‘C’ to suppose partition-1, ‘B’ to partition-2 and any thing else to partition-3. Directly using partition range function over this value column will only allow value ‘B’ to go to partition-1 as it is between ‘A’ and ‘C’ which are part of partition-1. To do away with we need to create a computed-persisted column in this table. The computed column will give us an indicator, based on a logic we define, where the partitions would be assigned.

Below code snippet adds a computed-persisted column with logic to generate a new value which can be assigned to partition range function. Idea here is to generate a same indicator for all the values that we want to put in one partition.

ALTER TABLE list_partition
ADD partition_flag AS ( CASE value WHEN 'A' THEN 1 WHEN 'B' THEN 2 WHEN 'C' THEN 1 ELSE 3 END ) PERSISTED

SELECT *
FROM list_partition
| ID | VALUE | PARTITION_FLAG |
-------------------------------
|  1 |     A |              1 |
|  2 |     B |              2 |
|  3 |     C |              1 |
|  4 |     D |              3 |
|  5 |     A |              1 |
|  6 |     B |              2 |
|  7 |     A |              1 |
|  8 |     E |              3 |
|  9 |     F |              3 |
| 10 |     G |              3 |

Let us check the number of partitions in current table (obviously it will be one)

SELECT partition_id , object_id , index_id , partition_number , rows
FROM sys . partitions
WHERE OBJECT_ID = OBJECT_ID ( 'list_partition' )
|      PARTITION_ID |  OBJECT_ID | INDEX_ID | PARTITION_NUMBER | ROWS |
-----------------------------------------------------------------------
| 72057595266007040 | 1668917017 |        0 |                1 |   10 |

Below code snippet does the following:
1) Create partition range function, it will create three partitions range for the following:
Partition 1 – Partition value less than and equal 1
Partition 2 – Partition value greater than 1 and less than equal to 2
Partition 3 – Partition value greater than 2
2) Create partition scheme and attach all the partitions to primary file group

CREATE PARTITION FUNCTION pf_list_partition ( INT )
AS RANGE LEFT
FOR VALUES ( 1 , 2 );

CREATE PARTITION SCHEME ps_list_partition
AS PARTITION pf_list_partition ALL TO ( [PRIMARY] )

--Partition scheme 'ps_list_partition' has been created successfully. 
--'PRIMARY' is marked as the next used filegroup in partition scheme 'ps_list_partition'.

Having reached here, in order to partition the table we need to create a clustered index on the partition_flag using partition scheme – as shown below

CREATE CLUSTERED INDEX CI_list_partition ON list_partition
(
id ASC
)
ON ps_list_partition ( partition_flag )

Let us take a look at the number of partions now

SELECT partition_id , object_id , index_id , partition_number , rows
FROM sys . partitions
WHERE OBJECT_ID = OBJECT_ID ( 'list_partition' )
|      PARTITION_ID |  OBJECT_ID | INDEX_ID | PARTITION_NUMBER | ROWS |
-----------------------------------------------------------------------
| 72057595266072580 | 1668917017 |        1 |                1 |    4 |
| 72057595266138110 | 1668917017 |        1 |                2 |    2 |
| 72057595266203650 | 1668917017 |        1 |                3 |    4 |

It is clear that there are three partitions with corresponding count of rows.

Woow…isn’t it a simple trick 🙂

Hope you enjoyed reading and ready to apply this!

Happy Learning
Lokesh Vij