Solved

sql datetime range filtering

Posted on 2011-09-28
5
192 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now