Datetime field to a date

[FB1]

MSCRM dynamics 4
SQL Visual studio 2008

Hi I have a field called scheduledstart which outputs value as 10/10/2010 00:08:30.

I have two datetime parameters
@start
@end

in my where clause
scheduledstart >= @start
and
scheduledstart <= @end

Perfect so far

but when I choose to pick 1st may to 31st may, it only shows up to 30th may because of the time factor.
How can I convert it to date field or even a datetime field showing 10/10/2010 00:00:00 as this would work
<= 10/10/2010.

I have tried convert(varchar,schedledstart,103) but this now a varchar not a date.

Thank you for all your time

 

[SgtJarrow]

You can try changing this line

scheduledstart <= @end

to

scheduledstart <= DATEADD(DAY, 1, @end)

This will set the end date to the 12:00:00 hour of the next day chosen, thereby including the day chosen.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[DaveInIowa]

The easiest (and in my opinion more correct) way would be to simply choose the next day as your @end value. This is what you're really looking for, date values BEFORE June 1st. Then use WHERE scheduledstart BETWEEN @start AND @end, which is equivalent to WHERE scheduledstart >= @start AND scheduledstart < @end (note that the 2nd part is strictly less than).

If you're set on using the last day of the month as the end parameter, try this:

CONVERT(DATETIME, FLOOR(CONVERT(NUMERIC(18,9), scheduledstart)))

 

[DaveInIowa]

Ignore the CONVERT/DATETIME/FLOOR... nonsense I posted and use Robert's DATEADD suggestion for the upper limit. You could still use the WHERE..BETWEEN structure or use just '<' for the upper limit as you don't want to include '2010-06-01 00:00:00'.