Avatar of Jeremy Campbell
Jeremy CampbellFlag 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
Avatar of peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

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.

Avatar of Azhrei1
Flag of Netherlands image

you could perhaps try datevalue(yyy/mm/dd)
Avatar of Jeremy Campbell


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..
Avatar of Jeremy Campbell
Jeremy Campbell
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Jeremy Campbell


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

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

Avatar of Jeremy Campbell


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

Avatar of 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.


 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 ().

Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo