Finish Date - How to query without the time element

[TroyS]

I have a procedure where the parameter is the task finish date.
I want the person to supply the day as the parameter (eg 12-14-05) without
having to specifiy the time component
For example, i want all tasks that have a finish date of 12-14-05 regardless
of whether they finish at 8am, noon, 5pm or whatever time.

my problem is that the finish date may be 12-14-05 17:00:00 in the project
schedule.
Unless the parameter is supplied as 12-14-05 17:00:00, the procedure won't
return results.

How do i truncate the time piece of datetime so that when the paramter is
supplied as 12-14-05, all tasks whose finish date is 12-14-05 xx:xx:xx are
returned regardless of what's in the xx:xx:xx's ..
thx.

 

[Ray McCoppin]

Try something like this

SELECT TASK_UID, TASK_FINISH_DATE
FROM MSP_TASKS
WHERE CONVERT(varchar(25), CAST(@mydate AS datetime), 1)
= CONVERT(varchar(25), TASK_FINISH_DATE, 1)

Cast your parmeter to datetime, when convert it to a format that does not
include the time and compare it to you field converted to the same format

 

[Mike Glen]

Hi Troy,

Try posting on the server newsgroup as this one is closing down. Please
see FAQ Item: 24. Project Newsgroups. FAQs, companion products and other
useful Project information can be seen at this web address:
http://project.mvps.org/faqs.htm .

Mike Glen
Project MVP