Need to convert datetime to date in SQL editor in Crystal Reports 2008

I'm not sure that you can convince CR that the result is a date, no matter what you do.
You could instead use a formula field within CR to give you a date with no time, or you can just format the datetime field itself if you are not using it it for comparisons.

Datepart({table.datetimefield})

you could perhaps try datevalue(yyy/mm/dd)

I'm trying to Create a parameter based on this field and just now realized I could do it as datetime although it is still not working (I originally thought It had to be just date in order for me to get the calender to select the date range from the parameter)

For some reason though it is still not working. It seems to be filtering out just random data..

I discovered I had a bad formula in my selection statement

Not sur ehow that causes the string vs date issue but if it solves it, great.

mlmcc

"(I originally thought It had to be just date in order for me to get the calender to select the date range from the parameter)" from my comment above..

CONVERT(date,StartDate,1) AS StartDate1 only gets recognized in Crystal as a string and would not let me use it as a date parameter.. The only way it would work was using it as datetime, which is the original format in the db. Then I could set the parameter as Datetime.. Although the time thing in the parameter selection is a little annoying.

Hopefully this clears things up a bit..

FWIW, the date data type was just added to MS SQL in the 2008 version.  Before that, there was just datetime.  My guess is that your version of CR still doesn't recognize the new date type.  I have no idea if any versions of CR recognize it yet.


 mlmcc,

 I don't think the change in the selection formula had anything to do with "the string vs date issue".  The date column being seen as string in CR was the original problem.  Then he changed the column to datetime instead and that fixed that issue (he could use a datetime parameter with the calendar).  But the record selection formula wasn't working properly, which turned out to be because of the missing ().

 James