Strange results when CAST DateTime to VARCHAR then use LIKE

Question

Strange results when CAST DateTime to VARCHAR then use LIKE

There is a column named OrderDate (data like this :2007-06-02 00:00:00.000)

I tried to get the date in 2007-06 with LIKE :

WHERE OrderDate LIKE '2007-06%'

but nothing worked. Then I tried CAST:

WHERE CAST(OrderDate AS VARCHAR) LIKE '2007-06%'

still got nothing. Anyone please help me here? I just want to use LIKE for this.

In addition, WHERE OrderDate LIKE '%2007%'

it can receive data from 2007, but "2007%" received nothing. New starter in SQL. THANK!!

+3

sql-server sql-like

IanJay Apr 29. 17 at 5:42

source to share

2 answers

Use YEAR

and MONTH

instead ofLIKE

WHERE 
    YEAR(OrderDate) = 2017 AND
    MONTH(OrderDate) = 6

You can see the result using the query below. You will see the problem.

Eg.

SELECT CAST(OrderDate AS VARCHAR) -- May 27 2017  1:22AM

0

NEER Apr 29. 17 at 6:47 am

source to share

Igor · Accepted Answer · 2017-04-29T06:43:10+0000

WHERE OrderDate >= '2007-06-01' AND OrderDate < '2007-07-01'

Use datetime instances to compare datetime, don't try to convert to string. The value '2007-06-01'

will be converted to DateTime, just like '2007-07-01'

. OrderDate must be of type Date or DateTime.

There are also built-in functions that you can use if you want to get a part of a date or date.

Edit

The reason this doesn't work WHERE CAST(OrderDate AS VARCHAR) LIKE '2007-06%'

is because you don't specify a length for varchar

. The next first seven characters of the date and time will be converted to a string.

WHERE CAST(OrderDate AS VARCHAR(7)) LIKE '2007-06%'

Another problem is that you are not guaranteed which format will be output when using broadcast. If you want line output, you must use CONVERT and specify the output format. For ISO8601 formatting, you do this.

WHERE CONVERT(VARCHAR(10), OrderDate, 126) LIKE '2007-06%'

Again, I really don't recommend this approach. Best random scenario, if there are any indexes on the data, it will ignore them, making your query very slow. Worst case, you make a mistake in your query using the wrong date-time comparison string, but it will never be caught because it is treated as a varchar (where, since my previous original query throws an error if you tried 2016-06-32

)

Strange results when CAST DateTime to VARCHAR then use LIKE

Edit

More articles: