Need to find date's between 2 date's in SQL Server 2008

Question

Hi

I need help with query to find all the dates that between 31/12/2009 and 31/02/2010

In SQL Server 2008

i try this:

SELECT  convert(varchar(50), MyDate, 103)
where convert(varchar(50), MyDate, 103) >= '31/12/2009' and convert(varchar(50), MyDate, 103) <='31/02/2010'

but it give me wrong result

why ?

Answer 1

BOL is always a good reference...

Start with BETWEEN

Answer 2

Take a look at:

Does BETWEEN with dates actually work in SQL SERVER 2008

Compare two DATETIME only by date not time in SQL Server 2008

Answer 3

I had a different interpretation of the question: "how to generate all the dates between a certain range?"

Here's a solution to that:

--define start and end limits
Declare @todate datetime, @fromdate datetime
Select @fromdate='2009-03-01', @todate='2009-04-10' 

;With DateSequence( Date ) as
(
    Select @fromdate as Date
        union all
    Select dateadd(day, 1, Date)
        from DateSequence
        where Date < @todate
)

--select result
Select * from DateSequence option (MaxRecursion 1000)

There is a nice article that shows how to generate sequences (numbers, dates, times) using CTEs.

---

Edit:

After clarification, the issue seems to be the date format being input: dd/mm/yyyy.

SQL Server expects the format mm/dd/yyyy.

I would simply transform it before running the select statement:

-- Assuming two variables, @inputFromDate and @inputToDate, in the format of dd/mm/yyyy...

declare @fromDate varchar(10), @toDate varchar(10)
set @fromDate = 
substring(@inputFromDate, 3, 2) + '/' + 
substring(@inputFromDate, 1, 2) + '/' + 
substring(@inputFromDate, 7, 4)

set @toDate = 
substring(@inputToDate, 3, 2) + '/' + 
substring(@inputToDate, 1, 2) + '/' + 
substring(@inputToDate, 7, 4)

select * from SomeTable where dateCol >= @fromDate and dateCol < @toDate
-- you can change the < or >= comparisons according to your needs

Answer 4

If the MyDate column is a datetime, as it appears to be, then it's already in the right "format". Don't convert it to a varchar(50) in the predicate condition - this makes your query non-sargable and will kill performance on any indexes you might have.

Instead, take your parameters as date or datetime instances:

SELECT ...
FROM MyTable
WHERE MyDate >= @BeginDate
AND MyDate <= @EndDate

Your query should not depend on a specific date format in the input parameters - those parameters are not varchar types, they are datetime (or date). When you run this query or stored procedure from whatever environment the application is in and supply binding parameters (you are using bind parameters, right?), said library will automatically handle any formatting issues.

If you try to use the >= and <= operators on character representations of dates, with any format other than the ISO standard yyyymmdd, you will get the wrong results, because the alphabetical order is different from the temporal order. Don't do this.

If you simply need to write an ad-hoc query, i.e. this isn't being run from any programming environment, then simply do not use the dd/mm/yyyy format. Use the ISO date format instead; it is unambiguous and implicitly convertible to datetime values:

SELECT ...
FROM MyTable
WHERE MyDate >= '20091231'
AND MyDate <= '20100231'

Honestly, no other solution is acceptable in my mind. For ad-hoc queries, always use the unambiguous ISO standard for dates. For applications connecting to the database, always use bind parameters. If you're doing anything else, you're writing code that's either unreliable, insecure, or both.