sql获得特定的日期时间

I want to write a SQL query to return results from 9PM yesterday until the current time. As far as I know, GETDATE() will only give me today's results, and GETDATE()-1 will give me all of yesterday, which I don't want.

我想写一个SQL查询来返回昨天晚上9点到现在的结果。据我所知，GETDATE()只会给我今天的结果，GETDATE()-1会给我昨天所有的结果，这是我不想要的。

How can this be accomplished?

这是如何实现的呢?

3 个解决方案

#1

select * from tbl
where datecol between dateadd(hour,21,DATEDIFF(d,0,getdate()-1))
  and getdate()

#2

Try with this:

试试这个:

DATE_SUB(CURDATE(), INTERVAL 24-9 HOUR);

This substracts 24-9 hours to the current date, which is equivalent to substracting 1 day and adding 9 hours.

这个减到当前日期的24-9小时，相当于减1天加9小时。

#3

-1

Try this, this works perfectly in Sybase databse

试试这个，这个在Sybase数据库中非常好用。

select * from table where datecol > dateadd(day,-1,dateadd(hour,18,(convert(datetime, convert(date, getdate())))))

从datecol > dateadd(day，-1,dateadd(hour,18, convert(datetime, convert, convert(date, getdate())))))的表中选择*

Explanation: (Inside to outside)

解说:(内,外)

convert(date, getdate()) -- This will get the today's date with only date part i.e., 7/19/2015

转换(date, getdate())——这将得到只有日期部分的今天的日期。,7/19/2015
convert(datetime, convert(date, getdate())) -- This will get the today's date with time as 00:00 hours i.e., 7/19/2015 12:00:00 AM

转换(datetime, convert(date, getdate())))——这将得到今天的日期，时间为00:00小时。上午,7/19/2015 12:00:00
dateadd(hour,21,(convert(datetime, convert(date, getdate()))) -- This will add the hours to previously obtained result i.e., 7/19/2015 9:00:00 PM

dateadd(hour,21，(convert(datetime, convert, convert(date, getdate()))))——这将使之前获得的结果增加小时数，即。下午,7/19/2015 9:00:00
dateadd(day,-1,dateadd(hour,18,(convert(datetime, convert(date, getdate()))))) -- Finally this will minus one day for the obtained date i.e., 7/18/2015 9:00:00 PM

dateadd(day，-1,dateadd(hour,18, convert(datetime, convert, convert, convert(date, getdate())))))——最终这将减去获得日期的1天，即。下午,7/18/2015 9:00:00

Here you go...

给你……

#1

select * from tbl
where datecol between dateadd(hour,21,DATEDIFF(d,0,getdate()-1))
  and getdate()

#2