Avatar of Jarred Meyer
Jarred Meyer
Flag for United States of America asked on

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

I am currently using this formula in the SQL editor but Crystal is picking this up as a string.
Anyone know how to modify this so it will recognized it as a Date?

CONVERT(date,StartDate,1) AS StartDate1

Thanks in advance!
Crystal ReportsMySQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
James0628

8/22/2022 - Mon
peter57r

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})
Azhrei1

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

ASKER
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..
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Jarred Meyer

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jarred Meyer

ASKER
I discovered I had a bad formula in my selection statement
Mike McCracken

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

mlmcc
Jarred Meyer

ASKER
"(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..

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
James0628

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