wrong sql query result - 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

General SQL Server Forums

New to SQL Server Programming

wrong sql query result

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-11 : 08:21:25

99pShop writes "I am trying to create a query to select all record for a specific 'PersonnelID'that have vacation booked in 2006 from a database.

I have written this query but it returns all PersonnelID that fit into the date block

requrirements

PersonnelID
select if DateStart falls within 2006
select if DateEnd falls within 2006

query i built:

Select * From tblResourceList Where PersonnelID=72
And tblResourceList.DateStart
Between '01 January 2006' And '31 December 2006'
Or tblResourceList.DateEnd Between '01 January 2006'
And '31 December 2006'
Or (tblResourceList.DateStart < '01 January 2006'
And tblResourceList.DateEnd > '31 December 2006')"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-11 : 08:27:39

Select * From tblResourceList
Where PersonnelID=72
And
(
tblResourceList.DateStart
Between '01 January 2006' And '31 December 2006'
Or tblResourceList.DateEnd Between '01 January 2006'
And '31 December 2006'
Or (tblResourceList.DateStart < '01 January 2006'
And tblResourceList.DateEnd > '31 December 2006')
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-11 : 08:30:54

Try using

'01/01/2006' instead of '01 January 2006'
and
'12/31/2006' instead of '31 December 2006'

If u want either the start date or end date to be in the yr 2006
the query can be written as

Select * From tblResourceList Where PersonnelID=72 And 
      (( tblResourceList.DateStart Between '01/01/2006' And '12/31/2006') OR
       ( tblResourceList.DateEnd   Between '01/01/2006' And '12/31/2006'))

Or if u want both the start date and end date to be in the yr 2006
the query can be written as


Select * From tblResourceList Where PersonnelID=72 And 
      (( tblResourceList.DateStart Between '01/01/2006' And '12/31/2006') AND
       ( tblResourceList.DateEnd   Between '01/01/2006' And '12/31/2006'))

Srinika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-11 : 09:47:35

It is better to use Universal format yyyy-mm-dd or yyyymmdd

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-12 : 02:35:01

quote:
Originally posted by madhivanan

It is better to use Universal format yyyy-mm-dd or yyyymmdd

Only yyyymmdd is universal, dash is national.

--
This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources