Solved

sql datetime range filtering

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore 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.
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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

679 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