SQL Syntax to convert datetime to date 1

[jchewsmith]

I am using a SQL view for a crystal 2011 report that I am writing

I used to be able to change all date times to date with a report option but I can't find that in Crystal 2011.

If this doesn't exist anymore then I need to be able to convert the datetime in the SQL view or when I am filtering it into Crystal?

 

[briangriffin]

In CR the formula would be: date(YourDateTime)

SQL Server 2008 and later has the date datatype, so: cast(YourDateTime as date)

Keep in mind that where you do the conversion and how you use it may impact report performance greatly.

 

[jchewsmith]

I am using SQL 2008 and when I try to cast(datefield as date) I get the following error:
Type date is not a defined system type?

Any ideas?

Thank You

 

[briangriffin]

Hmmm... I'm using 2008 R2, so that may have been where date made an appearance.

You can strip the time off a datetime field with this:

cast(Convert(varchar, YourDateTime, 101) as smalldatetime)

It will still be a datetime but you can format it as a date and filter as if it were a date.

 

[jchewsmith]

Thank you for your help.

Have a good day.