Start Free Trial
Come for the solution, stay for everything else.
Start Free Trial
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!
Microsoft SQL Server 2005
8/22/2022 - Mon
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.
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..
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.
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today
7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
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.
See how we're fighting big data
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
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.
"(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..
to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
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 ().
Plans and Pricing
Certified Expert Program
© 1996-2022 Experts Exchange, LLC. All rights reserved. Covered by US Patent