Microsoft KB Archive/284997

= PRB: Information About Use of LIKE Operator to Convert String to Datetime Query =

Article ID: 284997

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q284997



SYMPTOMS
When the day of the month is a single digit, an invalid input string format in a query, which uses the LIKE operator with a wildcard character %, may lead to unexpected results.



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.



WORKAROUND
Make sure that there are two spaces between the month and the day as in this example: 'Jan 3 2001%'



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: <pre class="fixed_text">DateT                                                 Book -- -- 2001-01-03 00:00:00.000                               aaa </li></ul>

Additional query words: like = % convert datetime string

Keywords: kbprb KB284997

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.