Click here to Skip to main content
15,881,172 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

SQL
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
Posted
Updated 7-Sep-16 6:01am
v3
Comments
John C Rayan 7-Sep-16 7:10am    
order by which date and time ?
Member 12363094 7-Sep-16 7:14am    
convert(varchar,convert(date, [Date], 103),101) this date and time is different column in table
Maciej Los 7-Sep-16 11:01am    
I do not see ORDER BY clause in your SELECT statement...
Member 12363094 7-Sep-16 11:07am    
i want to use order by in above query if i use this order by i am getting error.
where i have to use orderby column
Ankur\m/ 7-Sep-16 11:15am    
What is "[Date]" in this line of query - and convert(varchar,convert(date, [Date], 103),101) between @StartDate and @EndDate)?
Is it current date time or a column from rawdata table? If it's a column, what is its data type?

1 solution

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.

SQL
--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
 
Share this answer
 
v2
Comments
Member 12363094 7-Sep-16 12:25pm    
thanks but when i applied order by at end of query like urs i was getting error
David_Wimbley 7-Sep-16 12:33pm    
The query i posted gives no errors. It will execute, i ran it on my local machine. You had a few issues with your query and were using parenthesis galore. I removed the unnecessary parenthesis which may have been part of your problem.
Member 12363094 12-Sep-16 2:48am    
when i use CONVERT(varchar,convert(date, [Date], 103),101) then it is taking next date data if on that date data is not there

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900