Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sql datetime range filtering

Posted on 2011-09-28
5
207 Views
Last Modified: 2012-05-12
the attached scripts works fine except that the date sorting (1st column) appears out of chronological sync .. i.e.  01.08.2011 --> 31.08.2011 then 01.09.2011 --> 30.09.2011 for example  (what I am wanting to achieve)

01.08.2011      24 hr      677.41      0.00
01.09.2011      24 hr      518.14      0.00
02.08.2011      24 hr      609.20      0.00
02.09.2011      24 hr      483.57      0.00
03.08.2011      24 hr      670.73      0.00
03.09.2011      24 hr      436.65      0.00
04.08.2011      24 hr      628.32      0.00
04.09.2011      24 hr      426.75      0.00
05.08.2011      24 hr      620.92      0.00
05.09.2011      24 hr      485.35      0.00
06.08.2011      24 hr      687.25      0.00
06.09.2011      24 hr      511.57      0.00
07.08.2011      24 hr      460.47      0.00
07.09.2011      24 hr      547.90      0.00
08.08.2011      24 hr      624.00      0.00
08.09.2011      24 hr      471.41      0.00
09.08.2011      24 hr      537.07      0.00
09.09.2011      24 hr      497.86      0.00
10.08.2011      24 hr      498.74      0.00
10.09.2011      24 hr      445.15      0.00
11.08.2011      24 hr      561.31      0.00
11.09.2011      24 hr      476.55      0.00
12.08.2011      24 hr      528.71      0.00
12.09.2011      24 hr      495.41      0.00
13.08.2011      24 hr      438.91      0.00
13.09.2011      24 hr      509.83      0.00
14.08.2011      24 hr      474.04      0.00
14.09.2011      24 hr      568.16      0.00
15.08.2011      24 hr      431.97      0.00
15.09.2011      24 hr      538.48      0.00
16.08.2011      24 hr      505.44      0.00
16.09.2011      24 hr      520.65      0.00
17.08.2011      24 hr      486.55      0.00
17.09.2011      24 hr      534.93      0.00
18.08.2011      24 hr      490.56      0.00
18.09.2011      24 hr      554.62      0.00
19.08.2011      24 hr      468.64      0.00
19.09.2011      24 hr      579.58      0.00
20.08.2011      24 hr      554.73      0.00
20.09.2011      24 hr      499.77      0.00
21.08.2011      24 hr      504.42      0.00
21.09.2011      24 hr      520.93      0.00
22.08.2011      24 hr      449.12      0.00
22.09.2011      24 hr      528.93      0.00
23.08.2011      24 hr      469.30      0.00
23.09.2011      24 hr      578.94      0.00
24.08.2011      24 hr      499.30      0.00
24.09.2011      24 hr      605.06      0.00
25.08.2011      24 hr      425.15      0.00
25.09.2011      24 hr      488.90      0.00
26.08.2011      24 hr      416.33      0.00
26.09.2011      24 hr      588.03      0.00
27.08.2011      24 hr      419.23      0.00
27.09.2011      24 hr      576.87      0.00
28.08.2011      24 hr      430.84      0.00
28.09.2011      24 hr      159.62      0.00
29.08.2011      24 hr      492.70      0.00
30.08.2011      24 hr      466.77      0.00
31.08.2011      24 hr      494.62      0.00
USE [db1]

DECLARE @endDate   datetime
DECLARE @startDate datetime

SET @startDate = '2011-08-01 00:00:00.000'
SET @endDate   = '2011-09-30 23:59:59.997'

select 

convert(char(10), SetupTime,104) as 'DATE'

, '24 hr' AS 'PERIOD'

, cast( Cast(isnull(SUM(ChargeableAmount), 0) AS DECIMAL(18,2)) / 100000 AS DECIMAL(18,2)) AS 'TOTAL'
       
, cast( Cast(isnull(SUM(case LocationZoneID
                            when 167 then
                          ChargeableAmount end), 0) AS DECIMAL(18,2)) / 100000 AS DECIMAL(18,2))
	 AS 'CFW' 	 
			  
from [CDRData] 

WHERE (Direction = 1) 
	AND (COSID =95) 
	AND (SetupTime BETWEEN @startDate AND @endDate)
      
group by convert(char(10), SetupTime,104)
order by 1

Open in new window

0
Comment
Question by:amillyard
  • 2
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36716105
sorry
order by convert(datetime,convert(char(10), SetupTime,104))

instead of order by 1
0
 

Author Comment

by:amillyard
ID: 36716161
no worries - am getting the following error as follows:

Msg 242, Level 16, State 3, Line 9
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Warning: Null value is eliminated by an aggregate or other SET operation.

(line 9 - refers to the first SELECT statement)
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 36716245
try this
USE [db1]

DECLARE @endDate   datetime
DECLARE @startDate datetime

SET @startDate = '2011-08-01 00:00:00.000'
SET @endDate   = '2011-09-30 23:59:59.997'

select 

convert(char(10), SetupTime,104) as 'DATE'

, '24 hr' AS 'PERIOD'

, cast( Cast(isnull(SUM(ChargeableAmount), 0) AS DECIMAL(18,2)) / 100000 AS DECIMAL(18,2)) AS 'TOTAL'
       
, cast( Cast(isnull(SUM(case LocationZoneID
                            when 167 then
                          ChargeableAmount end), 0) AS DECIMAL(18,2)) / 100000 AS DECIMAL(18,2))
	 AS 'CFW' 	 
			  
from [CDRData] 

WHERE (Direction = 1) 
	AND (COSID =95) 
	AND (SetupTime BETWEEN @startDate AND @endDate)
      
group by convert(char(10), SetupTime,104)
order by convert(datetime, convert(char(10), SetupTime,104), 104)

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36716291
like this
USE [db1]

DECLARE @endDate   datetime
DECLARE @startDate datetime

SET @startDate = '2011-08-01 00:00:00.000'
SET @endDate   = '2011-09-30 23:59:59.997'

select convert(char(10),convert(datetime,[date]),104) as [Date], '24 hr' AS 'PERIOD',[total],[cfw]
from (
select 
convert(char(8), SetupTime,112) as 'DATE'
, cast( Cast(isnull(SUM(ChargeableAmount), 0) AS DECIMAL(18,2)) / 100000 AS DECIMAL(18,2)) AS 'TOTAL'
, cast( Cast(isnull(SUM(case LocationZoneID
                            when 167 then
                          ChargeableAmount end), 0) AS DECIMAL(18,2)) / 100000 AS DECIMAL(18,2))
	 AS 'CFW' 	 
			  
from [CDRData] 

WHERE (Direction = 1) 
	AND (COSID =95) 
	AND (SetupTime BETWEEN @startDate AND @endDate)
      
group by convert(char(8), SetupTime,112)
) as x
order by [date]

Open in new window

0
 

Author Closing Comment

by:amillyard
ID: 36716325
pratima_mcs:  works great.  many thanks :-)
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question