Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql datetime range filtering

Posted on 2011-09-28
5
Medium Priority
?
234 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
[X]
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
  • 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 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 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