Query with Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query with Dates

Author

Topic

tradingpassion
Starting Member

24 Posts

Posted - 2005-06-30 : 23:45:36

Hello

I want to run a query and display all the records that happen between lets' say Jan 1st 2005 to June 30 2005??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-01 : 00:57:17

Select * from yourTable where datecol between '01/01/2005' and '01/31/2005'

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-07-01 : 01:21:32

I personally would use

WHERE datecol >='01Jan2005'
AND datecol <'01Jul2005'

so that it is explicitly described that anything dated 01-Jul-2005 is NOT included.

Note that <='30Jun2005' would imply midnight 29/30 June, and thus would exclude anything ON 30-June that had a time latest than that, hence <'01Jul2005' - the absence of an explict time means midnight 30June/01July

Watch out for how you express the dates too - interpretation of '01/06/2005' and '06/01/2005' is down to locale settings, so you'd be better off with an unambiguous ISO format.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-01 : 01:33:24

Well
It is better to use 'dd-mmm-yyyy' or 'yyyymmdd' format to avoid local settings

Select * from yourTable where datecol >='200500101' and datecol<'20050701'

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-07-01 : 01:36:14

I must admit I use dd-mmm-yyyy a lot, because I find them easy to eye-ball. But I do so knowing that '01-Jun-2005' is not going to work on a French server ...

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-01 : 01:42:39

So how is it considered in French Server? In that case I think 'yyyymmdd' will be useful along with other formats

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-07-01 : 06:27:54

The French for June is Juin - so dd-mmm-yyyy would need to be '01-Jui-2005' on a French server :-(

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-01 : 06:53:37

Well
I knew new info today

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources