使用日期时间格式YYYYMMDDHHMMSS对1小时数据进行SQL查询

How to query for 1 hour data, my date-time format is YYYYDDMMHHMMSS?

如何查询1小时数据，我的日期时间格式是YYYYDDMMHHMMSS？

example:

例：

20120720094318
20120720121318
20120720144028

Tried finding the query online still doesn't work.

尝试在线查找查询仍然无法正常工作。

Tried:

尝试：

enddate >= FORMAT(DATEADD(dd,-1,GETDATE()), 'yyyyMMdd000000')

I would like something like this:-

我想要这样的事情： -

Sample data:

样本数据：

Cup     machine     end date
123      BB1         20120720092318
333      BB1         20120720094418
444      BB1         20120720084218
555      BB1         20120720082318

if i run the query @ 10am on above sample data i should have

如果我在上面的样本数据上运行查询@ 10am我应该有

Cup     machine     end date
123      BB1         20120720092318
333      BB1         20120720094418

which means to get record between 9am - 10am

这意味着要在早上9点到早上10点之间取得记录

2 个解决方案

#1

Try it like this (working in SQL-Server):

像这样试试（在SQL-Server中工作）：

This converts your date to "real" date-time datatype.

这会将您的日期转换为“真实”日期时间数据类型。

You might do this with alphanumeric values, but I wouldn't...

您可以使用字母数字值执行此操作，但我不会...

DECLARE @tbl TABLE(Cup INT,machine VARCHAR(100),[end] VARCHAR(100));
INSERT INTO @tbl VALUES
 (123,'BB1','20120720092318')
,(333,'BB1','20120720094418')
,(444,'BB1','20120720084218')
,(555,'BB1','20120720082318');

;WITH Converted AS
(
    SELECT Cup,machine,[end]   
          ,CONVERT(DATETIME,STUFF(STUFF(STUFF([end],9,0,' '),12,0,':'),15,0,':')) AS EndConverted 
    FROM @tbl      
)
SELECT *
FROM Converted
WHERE EndConverted BETWEEN {ts'2012-07-20 09:00:00'} AND {ts'2012-07-20 10:00:00'}

The result

结果

Cup machine end             EndConverted
123 BB1     20120720092318  2012-07-20 09:23:18.000
333 BB1     20120720094418  2012-07-20 09:44:18.000

#2

This is an effective/efficient way to truncate getdate() to just the current day, it gets the numbers of days since the calendar 0 reference date (1900-01-01), then it adds that number of days to the 0 reference date.

这是将getdate（）截断到当前日期的有效/高效方法，它获取自日历0引用日期（1900-01-01）以来的天数，然后将该天数添加到0引用日期。

dateadd(day, datediff(day,0, getdate() ), 0)

then add the current hour to that using hour(getdate())

然后使用小时（getdate（））将当前小时添加到

dateadd(hour,hour(getdate()),dateadd(day, datediff(day,0, getdate() ), 0))

so we arrive at the starting point of the range. To get the other end of the range, just add 1 more hour like so:

所以我们到达范围的起点。要获得范围的另一端，只需再添加1个小时，如下所示：

dateadd(hour,hour(getdate())+1,dateadd(day, datediff(day,0, getdate() ), 0))

then:

然后：

enddate >= FORMAT(dateadd(hour,hour(getdate()),dateadd(day, datediff(day,0, getdate() ), 0)), 'yyyyMMdd000000')
and enddate < FORMAT(dateadd(hour,hour(getdate())+1,dateadd(day, datediff(day,0, getdate() ), 0)), 'yyyyMMdd000000')

If it turns out your enddate column is a true datetime column, do NOT use the format() function because you can directly compare a datetime column to the calculated datetime value we have derived from getdate()

如果事实证明你的enddate列是一个真正的日期时间列，请不要使用format（）函数，因为你可以直接将datetime列与我们从getdate（）派生的计算日期时间值进行比较

i.e. dateadd() returns a datetime

即dateadd（）返回日期时间

enddate >= dateadd(hour,hour(getdate()),dateadd(day, datediff(day,0, getdate() ), 0))
and enddate < dateadd(hour,hour(getdate())+1,dateadd(day, datediff(day,0, getdate() ), 0))

note: I don't use or recommend using between for date ranges, use the combination >= with < instead.

注意：我不使用或建议在日期范围之间使用，使用组合> =和 <代替。< p>

To truncate getdate() an alternative approach is to use cast(getdate() as date) and with that as the basis you could use:

要截断getdate（），另一种方法是使用cast（getdate（）作为日期）并将其作为您可以使用的基础：

enddate >= FORMAT(dateadd(hour,hour(getdate()), cast(getdate() as date)), 'yyyyMMdd000000')
and enddate < FORMAT(dateadd(hour,hour(getdate())+1, cast(getdate() as date)), 'yyyyMMdd000000')

or, if not a varchar column but datetime:

或者，如果不是varchar列但是datetime：

enddate >= dateadd(hour,hour(getdate()), cast(getdate() as date))
and enddate < dateadd(hour,hour(getdate())+1, cast(getdate() as date))

#1