Get records of two periods of dates (range of two dates) 

SELECT MeterReadings.MeterNo, 
       MeterReadings.LastKWH, 
       MeterReadings.AccountNo, 
       MeterReadings.MultiplyFactor, 
       MeterReadings.ReadingDate, 
       MeterReadings.LastReading, 
       MeterReadings.ThisMonthReading, 
       MeterReadings.NumberofDays, 
       PeriodicConsumptions.ConsumptionKWH, 
       MeterReadings.ReadingGYear,
       MeterReadings.ReadingGMonth
FROM MeterReadings , PeriodicConsumptions 
WHERE 
((MeterReadings.AccountNo =PeriodicConsumptions.AccountNo )
 and (MeterReadings.ReadingGYear =PeriodicConsumptions.ConsumptionYear)
 and (MeterReadings.ReadingGMonth =PeriodicConsumptions.ConsumptionMonth )
 and (MeterReadings.ReadingDate = PeriodicConsumptions.FromDate )

 and ((MeterReadings.ReadingGYear = 2011 and MeterReadings.ReadingGMonth >= 6 ) 
 OR (((MeterReadings.ReadingGYear = 2012) AND (MeterReadings.ReadingGYear <= 2014))
 and MeterReadings.ReadingGMonth <= 6))) 

 and (MeterReadings.AccountNo= '4320900010') 
ORDER BY MeterReadings.ReadingDate

Result is: (for simplicity, I just put the year and month)

2011   6  
2011   7
2011   8
2011   9
2011  10
2011  11
2011  12
2012   1
2012   2
2012   3
2012   4
2012   5
2012   6

As per the query, the query should display all years and months of 2011, 2012, 2013 and 2014 with the exception of year 2011 to be displayed starting from  month 6 as specified and should stop at the displaying records at month 6 of year 2014.

I need to be able to display any range of two dates.

Pls, I need your help

PS: ReadingDate is saved as string type not as date type.

Thanks

Check the following way to achieve your requiement..

-- please check this demo query first 
Select DATEADD(day, DATEDIFF(day,0,GETDATE()),0),getdate(),DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0)

-- Now query for your need
Select * From CustomerGroup where TimeStampId between DATEADD(day, DATEDIFF(day,0,GETDATE()),0) and DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0)

--

and see this threads which are solved

http://forums.asp.net/t/1488856.aspx

http://forums.asp.net/t/1489235.aspx

Hi,

Try This

DECLARE  @test TABLE
(
ID INT IDENTITY,
ReadingDate VARCHAR(20)
)

INSERT INTO @test

SELECT 'Jan  7 2010  7:38PM' UNION ALL
SELECT 'Feb  7 2010  7:38PM' UNION ALL
SELECT 'Mar  7 2010  7:38PM' UNION ALL
SELECT 'Apr  7 2010  7:38PM' UNION ALL
SELECT 'May  7 2010  7:38PM' UNION ALL
SELECT 'Jun  7 2010  7:38PM' UNION ALL
SELECT 'Jul  7 2010  7:38PM' UNION ALL
SELECT 'Aug  7 2010  7:38PM' UNION ALL
SELECT 'Sep  7 2010  7:38PM' UNION ALL
SELECT 'Oct  7 2010  7:38PM' UNION ALL
SELECT 'Nov  7 2010  7:38PM' UNION ALL
SELECT 'Dec  7 2010  7:38PM' UNION ALL
SELECT 'Jan  7 2011  7:38PM' UNION ALL
SELECT 'Feb  7 2011  7:38PM' UNION ALL
SELECT 'Mar  7 2011  7:38PM' UNION ALL
SELECT 'Apr  7 2011  7:38PM' UNION ALL
SELECT 'May  7 2011  7:38PM' UNION ALL
SELECT 'Jun  7 2011  7:38PM' UNION ALL
SELECT 'Jul  7 2011  7:38PM' UNION ALL
SELECT 'Aug  7 2011  7:38PM' UNION ALL
SELECT 'Sep  7 2011  7:38PM' UNION ALL
SELECT 'Oct  7 2011  7:38PM' UNION ALL
SELECT 'Nov  7 2011  7:38PM' UNION ALL
SELECT 'Dec  7 2011  7:38PM' UNION ALL
SELECT 'Jan  7 2012  7:38PM' UNION ALL
SELECT 'Feb  7 2012  7:38PM' UNION ALL
SELECT 'Mar  7 2012  7:38PM' UNION ALL
SELECT 'Apr  7 2012  7:38PM' UNION ALL
SELECT 'May  7 2012  7:38PM' UNION ALL
SELECT 'Jun  7 2012  7:38PM' UNION ALL
SELECT 'Jul  7 2012  7:38PM' UNION ALL
SELECT 'Aug  7 2012  7:38PM' UNION ALL
SELECT 'Sep  7 2012  7:38PM' UNION ALL
SELECT 'Oct  7 2012  7:38PM' UNION ALL
SELECT 'Nov  7 2012  7:38PM' UNION ALL
SELECT 'Dec  7 2012  7:38PM' UNION ALL
SELECT 'Jan  7 2013  7:38PM' UNION ALL
SELECT 'Feb  7 2013  7:38PM' UNION ALL
SELECT 'Mar  7 2013  7:38PM' UNION ALL
SELECT 'Apr  7 2013  7:38PM' UNION ALL
SELECT 'May  7 2013  7:38PM' UNION ALL
SELECT 'Jun  7 2013  7:38PM' UNION ALL
SELECT 'Jul  7 2013  7:38PM' UNION ALL
SELECT 'Aug  7 2013  7:38PM' UNION ALL
SELECT 'Sep  7 2013  7:38PM' UNION ALL
SELECT 'Oct  7 2013  7:38PM' UNION ALL
SELECT 'Nov  7 2013  7:38PM' UNION ALL
SELECT 'Dec  7 2013  7:38PM' UNION ALL
SELECT 'Jan  7 2014  7:38PM' UNION ALL
SELECT 'Feb  7 2014  7:38PM' UNION ALL
SELECT 'Mar  7 2014  7:38PM' UNION ALL
SELECT 'Apr  7 2014  7:38PM' UNION ALL
SELECT 'May  7 2014  7:38PM' UNION ALL
SELECT 'Jun  7 2014  7:38PM' UNION ALL
SELECT 'Jul  7 2014  7:38PM' UNION ALL
SELECT 'Aug  7 2014  7:38PM' UNION ALL
SELECT 'Sep  7 2014  7:38PM' UNION ALL
SELECT 'Oct  7 2014  7:38PM' UNION ALL
SELECT 'Nov  7 2014  7:38PM' 




DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME

SET @STARTDATE='20110601'
SET @ENDDATE='20140630'

SELECT *
FROM @test
WHERE cast(floor(cast(CAST(ReadingDate AS DATETIME) as float)) as datetime) >= @StartDate
AND cast(floor(cast(CAST(ReadingDate AS DATETIME) as float)) as datetime) < DATEADD(DAY, 0, @EndDate)

--OUTPUT

--(59 row(s) affected)
--ID          ReadingDate
------------- --------------------
--18          Jun  7 2011  7:38PM
--19          Jul  7 2011  7:38PM
--20          Aug  7 2011  7:38PM
--21          Sep  7 2011  7:38PM
--22          Oct  7 2011  7:38PM
--23          Nov  7 2011  7:38PM
--24          Dec  7 2011  7:38PM
--25          Jan  7 2012  7:38PM
--26          Feb  7 2012  7:38PM
--27          Mar  7 2012  7:38PM
--28          Apr  7 2012  7:38PM
--29          May  7 2012  7:38PM
--30          Jun  7 2012  7:38PM
--31          Jul  7 2012  7:38PM
--32          Aug  7 2012  7:38PM
--33          Sep  7 2012  7:38PM
--34          Oct  7 2012  7:38PM
--35          Nov  7 2012  7:38PM
--36          Dec  7 2012  7:38PM
--37          Jan  7 2013  7:38PM
--38          Feb  7 2013  7:38PM
--39          Mar  7 2013  7:38PM
--40          Apr  7 2013  7:38PM
--41          May  7 2013  7:38PM
--42          Jun  7 2013  7:38PM
--43          Jul  7 2013  7:38PM
--44          Aug  7 2013  7:38PM
--45          Sep  7 2013  7:38PM
--46          Oct  7 2013  7:38PM
--47          Nov  7 2013  7:38PM
--48          Dec  7 2013  7:38PM
--49          Jan  7 2014  7:38PM
--50          Feb  7 2014  7:38PM
--51          Mar  7 2014  7:38PM
--52          Apr  7 2014  7:38PM
--53          May  7 2014  7:38PM
--54          Jun  7 2014  7:38PM

--(37 row(s) affected)

Thank you all for your replies. 

raghay, thank you for taken the time to create and give the result of your solution.

could you please  tell how to test the query on SQL server 2005 engine?

Thanks in advance

Thanks  Naom for the hint.

Here is how it can be tested on SQL sever.

SELECT MeterReadings.MeterNo, 
       MeterReadings.LastKWH, 
       MeterReadings.AccountNo, 
       MeterReadings.MultiplyFactor, 
       MeterReadings.ReadingDate, 
       MeterReadings.LastReading, 
       MeterReadings.ThisMonthReading, 
       MeterReadings.NumberofDays, 
       PeriodicConsumptions.ConsumptionKWH, 
       MeterReadings.ReadingGYear,
       MeterReadings.ReadingGMonth
FROM MeterReadings , PeriodicConsumptions 
WHERE 
((MeterReadings.AccountNo =PeriodicConsumptions.AccountNo )
 and (MeterReadings.ReadingGYear =PeriodicConsumptions.ConsumptionYear)
 and (MeterReadings.ReadingGMonth =PeriodicConsumptions.ConsumptionMonth )
 and (MeterReadings.ReadingDate = PeriodicConsumptions.FromDate ))
 and cast(floor(cast(CAST(ReadingDate AS DATETIME) as float)) as datetime) >= '20110601'   
 AND cast(floor(cast(CAST(ReadingDate AS DATETIME) as float)) as datetime) < DATEADD(DAY, 0, '20140630')
 and (MeterReadings.AccountNo= '4320900010') 
ORDER BY MeterReadings.ReadingDate

Why do you need to use all these manipulations? What was wrong with the original query I suggested?

What is the difference between '20140630' and dateadd(day,0,'20140630') ? 

 Hello Naom

First of all, thank you for your consideration.  In fact, in my application, I simply used your suggested query. You may have noticed that I was using two separated fields to manipulate the date. The fact is I did not know that I could directly play with the date field saved as string type!!!!  

I hop it's clear.

Thanks again.

While examining the difference between the two queries, I got the following points:

With Naom's suggested query: A separator such as slash in the string-date format (2011/12/01) is the only way to get precise results   

With Rayghav's suggested query: Will give precise results with and without using the slash.

I hop this helps.

Thanks

If the code I suggested didn't work as is, the way to get it to work would be

select * from (select fields, cast(StringDate as Datetime) as NewDateField from myTable where some conditions) X where NewDateField >='20110601' and NewDateField<'20140701'

in other words, you can cast your string date field to DateTime field (assuming your string date is always correct) and then compare with correct dates (ISO dates).