如何按以下查询的时间和日期排序 [英] How can I do order by time and date of a below query

问题描述

我想订购日期以及时间以下查询



这里的日期和时间是不同的栏目



我尝试了什么：



i已经尝试过以下一个

  ALTER  程序 [dbo]。[GetRawDataForPowerAndFuel]   -    3076，'06 -02-2016 00:00:00'，'06-02-2016 23:59:59 '，330  
（
  @ SiteId   INT ，
  @ StartDate   DATETIME ，
  @ EndDate   DATETIME ，
  @ TimeZone   int  
）
 作为 
 开始 
 
 声明  @ sitePid   varchar （ 50 ）
 选择 @ sitePid = sitenumber 来自 sites  where  siteId = @ siteId 
 
 
 
（select *，（Cast（Li_BattCurrentCharge1  as   float ）+（cast（Li_BattCurrentCharge2  as   float ））+（cast（Li_BattCurrentCharge3  as   float ））+ （cast（Li_BattCurrentCharge4  as   float ））） as  LiCurrentCharge，（Cast（Li_BattCurrentDischarge1  as   float ）+（cast（Li_BattCurrentDischarge2  as   float ））+（cast（Li_BattCurrentDischarge3  as   float ））+（强制转换（Li_BattCurrentDischarge4  as   float ） ））  LiCurrentDischarge 来自 rawdata 其中 SiteId = @ sitePid 
 和  convert （ varchar ， convert （ date ，[日期]， 103 ）， 101 ）之间  @ StartDate  和  @ EndDate ） 
 
  En d  
 
  GO  

解决方案

< blockquote>所以你的查询有一些问题，我认为已经纠正过了。但是，我认为这个答案太容易解决你的问题了。无论如何，我创建了两个var表来模拟用于运行查询的表，因为你没有提供任何架构来查看。



如果这不是'你的问题的答案，如果你想提供这个存储过程的架构以及2列包含你的日期/时间的指示，那么我肯定可以提供更准确的答案。

   -   这些存储的proc参数已转换变量 
  DECLARE   @ SiteId   INT ; 
  DECLARE   @ StartDate   DATETIME  ; 
  DECLARE   @ EndDate   DATETIME  ; 
  DECLARE   @ TimeZone   INT  ; 
 
   -   用于复制架构的var表 
  DECLARE   @ rawdata   TABLE （
 SiteId  INT   NULL ，
 Li_BattCurrentCharge1  VARCHAR （ 10 ） NULL ，
 Li_BattCurrentCharge2  VARCHAR （ 10 ） NULL ，
 Li_BattCurrentCharge3  VARCHAR （ 10 ） NULL ，
 Li_BattCurrentCharge4  VARCHAR （ 10 ） NULL ，
 Li_BattCurrentCharge5  VARCHAR （ 10 ） NULL ，
 
 Li_BattCurrentDischarge1  VARCHAR （ 10 ） NULL ，
 Li_BattCurrentDischarge2  VARCHAR （ 10 ） NULL ，
 Li_BattCurrentDischarge3  VARCHAR （ 10 ） NULL ，
 Li_BattCurrentDischarge4 < span class =code-keyword> VARCHAR （ 10 ） NULL ，
 Li_BattCurrentDischarge5  VARCHAR （ 10 ） NULL ， 
 [日期]  VARCHAR （ 200 ） NULL  
）; 
 
  DECLARE   @ sites   TABLE （
 siteId  INT   NULL ，
 sitenumber  VARCHAR （ 50 ） NULL  
）; 
 
   -   开始模拟SP  
  BEGIN  
 
 
  DECLARE   @ sitePid   varchar （ 50 ）; 
 选择 @sitePid =（ SELECT  sitenumber 来自  @ sites  其中 siteId = @ siteId）
 
 
 
  SELECT  *，
（Cast（Li_BattCurrentCharge1  as   float ）+ 
 CAST（Li_BattCurrentCharge2  as   float  ）+ 
 CAST（Li_BattCurrentCharge3  as   float ）+ 
 CAST（Li_BattCurrentCharge4  as   float ）） AS  LiCurrentCharge，
 
（CAST（Li_BattCurrentDischarge1  as   float ）+ 
 CAST（Li_BattCurrentDischarge2  as   float ）+ 
 CAST（Li_BattCurrentDischarge3  as   float ）+ 
 CAST（Li_BattCurrentDischarge4  as   float ）） as  LiCurrentDischarge 
 
  FROM   @rawdata  
  WHERE  SiteId = @ sitePid  AND  
  CONVERT （ varchar ， convert （ date ，[日期]， 103 ），< span class =code-digit> 101 ） BETWEEN   @ StartDate  和  @ EndDate  
  ORDER   BY  [日期] 
 
 结束 
 
  GO  

I want to orderby date and as well as time in below query

here date and time are different columns

What I have tried:

i have tried below one

ALTER Procedure [dbo].[GetRawDataForPowerAndFuel]-- 3076,'06-02-2016 00:00:00','06-02-2016 23:59:59',330
(
   @SiteId INT,
   @StartDate DATETIME,
   @EndDate DATETIME,
   @TimeZone int
)
As
Begin

declare @sitePid varchar(50)
select @sitePid=sitenumber from sites where siteId=@siteId 



(select*,(Cast(Li_BattCurrentCharge1 as float)+(cast(Li_BattCurrentCharge2 as float))+(cast(Li_BattCurrentCharge3 as float))+(cast (Li_BattCurrentCharge4 as float))) as LiCurrentCharge, (Cast(Li_BattCurrentDischarge1 as float)+(cast(Li_BattCurrentDischarge2 as float))+(cast(Li_BattCurrentDischarge3 as float))+(cast(Li_BattCurrentDischarge4 as float)))as LiCurrentDischarge from rawdata where SiteId=@sitePid  
 and convert(varchar,convert(date, [Date], 103),101)  between @StartDate and @EndDate)
              
End

GO

解决方案

So your query had a few issues with it that I think have been corrected. But also, i think this answer is too easy to what your question is. Anyway, I created two var tables to simulate the tables you used to run the query against as you didn't provide any schema to look at.

If this isn't the answer to your question, If you would like to provide your schema for this stored proc along with the indication of what 2 columns contain your date/time then I'm sure a more accurate answer can be provided.

--These stored proc params converted to variables
DECLARE @SiteId INT;
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
DECLARE @TimeZone INT;

--var tables to replicate your schema
DECLARE @rawdata TABLE (
	SiteId INT NULL,
	Li_BattCurrentCharge1 VARCHAR(10) NULL,
	Li_BattCurrentCharge2 VARCHAR(10) NULL,
	Li_BattCurrentCharge3 VARCHAR(10) NULL,
	Li_BattCurrentCharge4 VARCHAR(10) NULL,
	Li_BattCurrentCharge5 VARCHAR(10) NULL,

	Li_BattCurrentDischarge1 VARCHAR(10) NULL,
	Li_BattCurrentDischarge2 VARCHAR(10) NULL,
	Li_BattCurrentDischarge3 VARCHAR(10) NULL,
	Li_BattCurrentDischarge4 VARCHAR(10) NULL,
	Li_BattCurrentDischarge5 VARCHAR(10) NULL,
	[Date] VARCHAR(200) NULL
);

DECLARE @sites TABLE (
    siteId INT NULL,
	sitenumber VARCHAR(50) NULL
);

--Left the begin to "simulate" the SP
BEGIN

 
DECLARE @sitePid varchar(50);
select @sitePid=(SELECT sitenumber from @sites where siteId=@siteId)
 

 
SELECT *,
(Cast(Li_BattCurrentCharge1 as float)+
 CAST(Li_BattCurrentCharge2 as float)+
 CAST(Li_BattCurrentCharge3 as float)+
 CAST(Li_BattCurrentCharge4 as float)) AS LiCurrentCharge, 
 
 (CAST(Li_BattCurrentDischarge1 as float)+
 CAST(Li_BattCurrentDischarge2 as float)+
 CAST(Li_BattCurrentDischarge3 as float)+
 CAST(Li_BattCurrentDischarge4 as float))as LiCurrentDischarge 
 
 FROM @rawdata 
 WHERE SiteId=@sitePid  AND 
  CONVERT(varchar,convert(date, [Date], 103),101) BETWEEN @StartDate and @EndDate
	ORDER BY [Date]         

End
 
GO

这篇关于如何按以下查询的时间和日期排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！