SQL Like Statement on a Datetime Type

SQL Server datetime LIKE select?

You could use the DATEPART() function

SELECT * FROM record 
WHERE  (DATEPART(yy, register_date) = 2009
AND    DATEPART(mm, register_date) = 10
AND    DATEPART(dd, register_date) = 10)

I find this way easy to read, as it ignores the time component, and you don't have to use the next day's date to restrict your selection. You can go to greater or lesser granularity by adding extra clauses, using the appropriate DatePart code, e.g.

AND    DATEPART(hh, register_date) = 12)

to get records made between 12 and 1.

Consult the MSDN DATEPART docs for the full list of valid arguments.

SQL datetime LIKE select - why do I need an extra %?

The DATETIME is converted to a VARCHAR before the comparison, and there definitely is no guarantee that the conversion will be in the pattern you mention. DATETIME is not stored internally as a VARCHAR but as a FLOAT.

How to query DATETIME field using only date in Microsoft SQL Server?

use range, or DateDiff function

 select * from test 
 where date between '03/19/2014' and '03/19/2014 23:59:59'

or

 select * from test 
 where datediff(day, date, '03/19/2014') = 0

Other options are:

1. If you have control over the database schema, and you don't need the
   time data, take it out.

2. or, if you must keep it, add a computed column attribute that has the time portion of the date value stripped off...

Alter table Test
Add DateOnly As
DateAdd(day, datediff(day, 0, date), 0)

or, in more recent versions of SQL Server...

Alter table Test
Add DateOnly As
Cast(DateAdd(day, datediff(day, 0, date), 0) as Date)

then, you can write your query as simply:

select * from test 
where DateOnly = '03/19/2014'

LIKE%' is not working in update query while comparing datetime in database

You need to first convert your date time to string, then you can use LIKE.

You can use Convert() and Cast(). But better:

WHERE  DATE_FORMAT(dateColumn, '%Y-%m-%d') LIKE '2016-10-%'

See this answer

mysql datetime field with index get a range 'like' vs. 'between and' performance

Assuming that t.active_time's type is DATETIME,

The following query cannot use an index because of the function call. All active_time must be converted on-the-fly to a DATE value before comparison with '2013-06-30'. This string is converted to a DATE value in the very first place, and this happens only once at the very beginning of the query.

SELECT * FROM subscription t WHERE DATE(t.active_time) = '2013-06-30';

The second query cannot use an index either for similar reasons. You are actually making a string comparison (because of the LIKE operator). All active_time values are converted to a string on-the-fly.

SELECT * FROM subscription t WHERE t.active_time LIKE '2013-06-30%';

Only the last one can use an index. The strings '2007-11-30' and '2007-12-01' are cast to DATETIME in this case, because the < and > operators allow this.

SELECT * FROM subscription t WHERE t.active_time > '2007-11-30' AND t.active_time < '2007-12-01';

The latter also applies to the = and BETWEEN operators.

For information, all types can be compared to a string with the LIKE operator, suffering from the same problem as described above, because of the implicit conversion it requires.

t.active_time = '2013-06-30' does not work as expected because '2013-06-30' is cast to a DATETIME value, that is to say '2013-06-30 00:00:00'.

---

Related Topics