Weird results when CAST DateTime to VARCHAR, then use LIKE

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

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

WHERE OrderDate LIKE '2007-06%'

but couldn't get anything. 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 to do this.

Also, WHERE OrderDate LIKE '%2007%' , could get data start with 2007, but '2007%' got nothing. A new starter in SQL. THANKS!!

Solution

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

Use datetime instances to compare datetimes, do not try to convert to string. The value '2007-06-01' will be converted to a DateTime as will '2007-07-01'. OrderDate should already be of type Date or DateTime.

There are also built in functions you can use if you wanted to retrieve a part of a Date or DateTime.

Edit

The reason this does not work WHERE CAST(OrderDate AS VARCHAR) LIKE '2007-06%' is because you do not specify a length for varchar. The following would take the first 7 characters of the datetime once its converted to string.

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

The other problem is you are not guaranteed which format will be output when you use cast. If you want a string output you should use CONVERT and specify the output format. For ISO8601 formatting you this.

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

Again, I really do not recommend this approach. Best case scenario if there are any indexes on the data it will ignore them making your query very slow. Worst case scenario you make a mistake in your query using an improper datetime comparison string but it is never caught because it is treated as a varchar (where as my above original query would raise an error if you tried 2016-06-32)