Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql datetime range filtering

Posted on 2011-09-28
5
Medium Priority
?
238 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 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

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…
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 …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

916 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