使用小时，分钟使用提取功能时出现错误 [英] Getting error when using Hour,minute using extract function

问题描述

我有一个包含多个列的SQL查询，其中包括没有时间的timestamp类型.我想输出时间以及count(*)并按小时和每15分钟将数据分组.我写的查询如下所示.

但是，它不起作用.

查询:
-------

  SELECT  CAST(Ptime  as  日期) AS  dt，
       提取(小时来自 Ptime) as  hr，
       floor(从( Ptime提取(分钟分钟")/15)*  15   AS  mn，
       count(*)为计数
 FROM 日志位置 CAST(Ptime  As  日期)> = '  2010/09/23'
组  BY  CAST(Ptime  as  日期)，
         提取(小时来自 Ptime)，
         floor(从( Ptime提取(分钟>分钟")/15)* 订单  BY  dt，hr，mn 

解决方案

使用datepart函数可以解决您的问题

像:-

  SELECT  CAST(Ptime  as   DateTime ) AS  dt，datepart(month，CAST(Ptime  as   DateTime ))，datepart(小时，CAST(Ptime  as   DateTime ))，datepart(分钟，CAST(Ptime  as   DateTime ))

SQL 2005 DateTime函数 [SELECT CAST(Ptime as Date) AS dt, extract(hour from Ptime) as hr, floor(extract(minute from Ptime)/15) * 15 AS mn, count(*) as thecount FROM log WHERE CAST(Ptime As Date) >= '09/23/2010' GROUP BY CAST(Ptime as Date), extract(hour from Ptime), floor(extract(minute from Ptime)/15) * 15 ORDER BY dt, hr, mn

解决方案

using datepart function you can solve your problem

like :-

SELECT CAST(Ptime as DateTime) AS dt,datepart(month,CAST(Ptime as DateTime)),datepart(hour,CAST(Ptime as DateTime)),datepart(minute,CAST(Ptime as DateTime))

SQL 2005 DateTime Functions[^]

这篇关于使用小时，分钟使用提取功能时出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！