Date Query using 'BETWEEN' 1

[seb2]

What's the proper SQL for selecting all records where a specific date field is between 2 dates?

I know I can accomplish it using a long 'where' statement... but want to use 'between'

Select*
from table
where table.completed_date between '01/02/02' and '04/01/02';

Doesn't seem to work,
Help!

 

[carrr]

It would be helpful to know the error message you receive when attemptng to execute this query, but here are a few possibilities:

1) drop the ; at the end of your query

or

2) your date format may demand full year rather than 02

If this doesn't work, reply with the error message shown in your results window...

 

[tlbroadbent]

The statement looks OK. It is important to remember that SQL Server defaults to American date format. The dates are assumed to be in mm/dd/yy format. Are your dates in that format? Is completed_date a datetime data type?

Check out my web page about "handling dates in SQL Server." You may find some helpful information.

http://tlbroadbent.home.attbi.com/sql_server_datetime.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[seb2]

completed_date is 'date' not 'datetime' data type. when inserting into the table I used

TO_DATE('5/4/02','MM/DD/YY')

My query using the following works, so I don't think I have a problem with the date format - it rather lies with my usage of 'BETWEEN' with dates
where completed_date >= '01/01/02' AND completed_date <= '05/01/02'

BTW - You've got a great site!

Thanks

 

[NickISD]

So there's never any confusion I always use dates filters as dd/mmm/yyyy, this way SQL server can never or should never get it wrong, at least on UK settings.

A between set as SELECT * From MyTable WHERE dTradingDate BETWEEN '01/Jan/2002' AND '31/Jan/2002' works just fine.

Hope this helps.

Regards, Nick

 

[cmmrfrds]

'for 2 digit year
where convert(char(8),table.completed_date ,1) between '01/02/02' and '04/01/02';

'for 4 digit year
where convert(char(10),table.completed_date ,101) between '01/02/2002' and '04/01/2002';

 

[seb2]

Thanks! You guys are great!

 

[tlbroadbent]

1) There is no DATE data type in SQL Server.
2) TO_DATE is not a SQL Server T-SQL function.

Are you actually using SQL Server? Are you querying a linked table? A little more info would be helpful.

In SQL Server there is absolutely no need to convert date columns to character data type in order to compare to a character string. The following will work assuming the datecolumn is a datetime or smalldatetime data type and the criteria is in mm/dd/yy format.

Select * From table
Where datecolumns between '1/2/02' amd '4/1/02'

I agree with Nick regarding the use of non-ambiguous date formats. I've always preferred mmm dd yyyy.

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[cmmrfrds]

Terry, on converting the date to a short date.

Select * From table
Where datecolumns between '1/2/02' amd '4/1/02'

So, this query will pick up a date of '4/1/02 23:15:00' as one would want using between as above. My users expect the ending date to be fully included even if they work until 11 pm. I have found the syntax used above not to do that.

 

[tlbroadbent]

cmmrfrds,

No argument here on your point. However, we are discussing date rather than date and time. I avoid using functions on columns in the Where clause because that can dramatically decrease performance. To include all records for a date regardless of time simply add 1 day to ending date and use the following syntax.

Select * From table
Where datecolumn >= '1/2/02'
And datecolumn < '4/2/02' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[cmmrfrds]

Thanks Terry, I agree between should be avoided since there are too many ways to misunderstand what will happen under all circumstances, in addition, to the extra processing load.