How to return Tasks based on Finish Date regardless of 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 the 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.

 

[Rod Gill]

Date-time is stored as a real (or single) number. The fractional part is the
percentage of 24 hours that has past. EG .5 is 12 mid-day. So, to remove the
time part the simplest way is to convert to a Long using:
Clng(DateTime value)

Note: in VBA Long values are quicker to process than Integers since a Long
variable uses a full 32 bit word. Integers required extra processing to use
only 16 bits. So even though Long processing intuitively feels like it's
more processor intensive, it actually isn't!

 

[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

 

[Jan De Messemaeker]

Hi,

In VBA, use the DateValue function, it returns the date without time of day.
HTH