I am using a calendar control to pass a date to a stored procedure. The field in the table is a datetime field. Is it possible to extract just the date from the datetime field, or do I have to use multiple Datepart?
WHERE (datepart(mm,sampletimestamp) = month(@.selcteddate) and
datepart(dd,sampletimestamp) = day(@.selcteddate) and
datepart(yyyy,sampletimestamp) = year(@.selcteddate)
)
This works, but I thought there must be an easier way.
There are many ways. Easiest is to do below:
convert(varchar, sampletimestamp, 112) = @.selcteddate
|||Something like this:
select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())),
DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))
-- --
2007-01-10 00:00:00.000 2007-01-11 00:00:00.000
And best to use a form like this for your where:
WHERE sampletimestamp >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, @.selcteddate))
and sampletimestamp < DATEADD(DAY, 1, DATEDIFF(DAY, 0, @.selcteddate))
So you can increase the likelihood of using an index for the search, since you don't have to execute a function on the column (which makes it unusable as a search argument for an index lookup.)
No comments:
Post a Comment