PRB: Information About Use of LIKE Operator to Convert String to Datetime Query (284997)

CAUSE

When you use the LIKE operator with a wildcard character (%), SQL Server first converts the specified date (enclosed in the single quotation marks) to a datetime format, and then converts the date to a varchar string. When converting the date enclosed in the single quotation marks along with wildcard character (%) to a datetime format, SQL Server rejects all values that it cannot recognize as a date. When the day of the month is a single digit, if you do not place two spaces between the month and the day, SQL Server does not recognize the value as a valid datetime format, which may therefore lead to unexpected results.

MORE INFORMATION

Steps to Reproduce Behavior

Run this code:

USE pubs
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[table1]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[table1]
GO
CREATE TABLE [dbo].[table1] (
	[Book] [char] (10) NOT NULL,
        [DateT] [datetime] NOT NULL 
) ON [PRIMARY]
GO
INSERT INTO table1 (Book,DateT) VALUES('aaa', ' Jan 03 , 2001')
GO
INSERT INTO table1 (Book,DateT) VALUES('bbb', ' Dec 31 , 2000')
GO

Then, run this code:

SELECT * FROM table1
GO

The preceding Transact-SQL commands produce the following result:


Book       DateT                                             
---------- ------------------------------------------------------ 
aaa        2001-01-03 00:00:00.000
bbb        2000-12-31 00:00:00.000

Pattern Matching with the LIKE Operator

SQL Server Books Online recommends that you use the LIKE when you search for datetime values, because datetime entries may contain a variety of date parts. However, an invalid input string following the LIKE operator along with the wildcard character (%) may cause unexpected results.

The following example shows the LIKE operator converting a string to a datetime in a query, which works:

SELECT DateT, Book FROM table1 WHERE DateT LIKE 'Dec 31 2000%' ORDER BY DateT
GO

The preceding Transact-SQL command returns the following result:


DateT                                                  Book      
------------------------------------------------------ ----------- 
2000-12-31 00:00:00.000                                bbb

If you run the code that follows, which has an incorrect input string 'Jan 03 2001%', you do not get any results:
```
SELECT DateT, Book FROM table1 WHERE DateT LIKE 'Jan 03 2001%' ORDER BY DateT  
GO
					
```
The preceding Transact-SQL command does not return any results.
If you run the code that follows, which has an incorrect input string 'Jan 3 2001%', does not return any result:
```
SELECT DateT, Book FROM table1 WHERE DateT LIKE 'Jan 3 2001%' ORDER BY DateT 
GO
					
```
The preceding Transact-SQL command does not return any result.

The following code example inputs the correct string format:

SELECT DateT, Book FROM table1 WHERE DateT LIKE 'Jan  3 2001%' ORDER BY DateT 
GO

Note that there are two spaces between Jan and 3.

The preceding Transact-SQL command returns the following result:

DateT                                                  Book
------------------------------------------------------ ---------- 
2001-01-03 00:00:00.000                                aaa