Check for dates between 2 supplied dates

[Naoise]

I have posted this on the MSSQL board but have not received any working solutions. Seeing as this is a fairly common query I would like to try an ANSI SQL solution.

tblA
===============
ID[int] | TheDate [datetime]
1 | 2006-04-06 11:25:58.920
2 | 2006-04-13 11:36:42.000


I was using the following to select all dates between 2 supplied dates. I would like to compare
results in format 103 ie dd/mm/yyyy

SELECT convert(varchar(30),TheDate,103)
FROM tblA
WHERE convert(varchar(30),TheDate,103) >= '06/04/2006'
AND convert(varchar(30),TheDate,103) <= '13/04/2006'

The shortcomings of this approach are evident when you use a date like '06/06/2006' as it still returns results from April

Bearing in mind that bad dates like 31/04/06 can be passed into any sql what is the best
way of accomplishing this?

Thanks for replies

 

[LKBrwnDBA]

Do it the other way around:

SELECT convert(varchar(30),TheDate,103) 
  FROM tblA
 WHERE TheDate BETWEEN convert(varchar(30),'06/04/2006',103)
   AND convert(varchar(30),'13/04/2006',103)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[gmmastros]

Your original method would work if your login is set to a language that uses day-month-year format. In cases like this, it is best to use ISO dates. So...

06/04/2006 => 20060406
13/04/2006 => 20060413

SELECT convert(varchar(30),TheDate,103) 
FROM tblA
WHERE TheDate >= '20060406'
AND TheDate <= '20060413'

Using ISO dates will never confuse SQL Server. For a more in-depth explanation of dates (in SQL Server), please see this thread. thread183-1240616



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PHV]

I thought that ISO DateTime were 'YYYY-MM-DD HH:MM:SS'

 

[gmmastros]

I don't know about the ISO date format. I do know about SQL Server. I know that with the dashes in the date, SQL Server can still get 'confused' while interpreting dates. Try this script in Query Analyzer to prove it to yourself.

Set DateFormat mdy

Select Month('2006-06-04') As WithDashes, 
       Month('20060604') As WithoutDashes

Set DateFormat dmy

Select Month('2006-06-04') As WithDashes, 
       Month('20060604') As WithoutDashes

The results are...

[tt][blue]
WithDashes WithoutDashes
----------- -------------
6 6

(1 row(s) affected)

WithDashes WithoutDashes
----------- -------------
[red]4[/red] 6

(1 row(s) affected)
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PHV]

So, again, seems that SQL Server is not fully ANSI compliant ?

 

[r937]

i'm not surprised it got confused, george

you told it to use dateformat dmy but then you fed it year-month-day



r937.com | rudy.ca

 

[gmmastros]

Rudy,

I'm with ya. Seriously.

The date format is set for every login depending on the default language for the login. If you prefer...

Set language 'english'
Select Month('2006-06-04') As WithDashes, 
       Month('20060604') As WithoutDashes

set language 'british'

Select Month('2006-06-04') As WithDashes, 
       Month('20060604') As WithoutDashes

I would be interested to know if you get the same results.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[r937]

yes, same results

WithDashes WithoutDashes
----------- -------------
6 6

WithDashes WithoutDashes
----------- -------------
4 6

that's pretty weird, eh

r937.com | rudy.ca