如何在Like operaror SQL SERVER中使用子查询 [英] how to use subquery in Like operaror SQL SERVER

问题描述

I want a query like this

select CONVERT(VARCHAR(20),[date] , 120) from timesheetentry 
where [date] LIKE  (SELECT convert(varchar(10), getdate(), 126))%


  i want to select the date where the date like in subquery can any help me to solve this

推荐答案

试试这个...

Try this...

SELECT CONVERT(VARCHAR,[date] , 120) DateColumn from timesheetentry 
WHERE CONVERT(VARCHAR,[date],126) LIKE ''+ CONVERT(VARCHAR, GETDATE(), 126))+'%'

快乐编码：）

Happy Coding :)

试试这个查询

Try this query

declare 
	@date varchar(10);
select 
	@date = convert(varchar(10), getdate(),126);
select 
	convert(varchar(20),[date] , 120) from timesheetentry 
where 
	[date]>= @date+' 00:00:00.000' and [date]< @date+' 23:59:59:998'

以上解决方案正常工作，但我认为将日期值比较为字符串（varchar）不是一个好主意，因为它将限制只检查一个值（日期）并且不能在其他运算符之间使用。 />


根据您的问题，您需要比较日期时间值的日期部分而不是时间部分，因此查询将是：

Above solutions work properly, but I think comparing date values as string (varchar) is not a good idea as it will limit to check just one value(date) and cannot use between and other operators.

According to your question you need to compare just the date part of the datetime value not the time part so the Query will be :

select CONVERT(VARCHAR(20),[date] , 120) from timesheetentry
where Convert(DateTime,Convert(Char(10),[date],120),120) = Convert(DateTime,Convert(Char(10),getdate(),120),120) 

这样，时间部分将从Datetime值中排除，Datetime运算符（之间）也可以用于比较。

this way the time part will be excluded from the Datetime value and Datetime operators(between) can also be used for comparisons.

这篇关于如何在Like operaror SQL SERVER中使用子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！