Date Selection - syntax - 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 2005 Forums

Transact-SQL (2005)

Date Selection - syntax

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-09 : 12:23:56

I have a sp that selects on date:

JobNumber int,
@ItemNumber char(20),
@NoteDate datetime

as

select jobnoteevent.note
from dbo.jobnoteevent
	inner join event on event.eventguid = jobnoteevent.eventguid
	inner join batch on batch.batchguid = event.batchguid
	inner join job on job.jobguid = event.jobguid
	inner join item on item.itemguid = event.itemguid
where job.companyjobid = @JobNumber and item.companyitemid = @ItemNumber and batch.reportdate = @NoteDate

Is the syntax for the date select correct?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-09 : 12:38:08

Looks ok. Why, is it giving an error or returning the wrong results?

Bear in mind that a datetime has a time portion as well as a date. When you use an equality, the two values must be the same, down to the millisecond, for a row to be returned.

--
Gail Shaw
SQL Server MVP

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-09 : 12:39:50

Can I just select on date only?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-09 : 12:44:27

quote:
Originally posted by snufse

Can I just select on date only?

you mean while executing SP.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 12:52:07

you can strip timepart by using like this

DATEADD(d,DATEDIFF(d,0,batch.reportdate),0)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-09 : 13:52:10

Bearing in mind that any function on a column will prevent SQL from using an index on that column (should one exist)

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-09 : 14:06:55

[code]JobNumber int,
@ItemNumber char(20),
@NoteDate datetime

as

set @NoteDate = datediff(day, '19000101', @notedate)

select jobnoteevent.note
from dbo.jobnoteevent
inner join event on event.eventguid = jobnoteevent.eventguid
inner join batch on batch.batchguid = event.batchguid
inner join job on job.jobguid = event.jobguid
inner join item on item.itemguid = event.itemguid
where job.companyjobid = @JobNumber and item.companyitemid = @ItemNumber
and batch.reportdate >= @NoteDate
and batch.reportdate < dateadd(day, 1, @NoteDate)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources