Convert Date in Crystal Reports and or in a SQL View

Posted on 2009-04-28
Last Modified: 2012-05-06
Hello, I have a date field in SQL Server that I need to convert in my Crystal Report. I would love to know the syntax to use in a View in SQL also if you happen to know.
An example of the date is 733497.
I am using Crystal XI if this helps.
Thanks for your time.
Question by:SimonSaysSQL
    LVL 4

    Expert Comment

    I always cast date values as text and pass them between SQL and Crystal as text values. I've found this to be the most bullet proof approach.
    LVL 18

    Expert Comment

    How do you want the date to appear in your report?

    Author Comment

    I would still have to look up how to get the right syntax.
    January 23, 2009 would be fine. But once I see one example I hope I can make changes if needed. I never know what they will ask for on the reports.
    LVL 18

    Accepted Solution

    SELECT CAST(39834 AS datetime) returns 2009-01-23 00:00:00.000 on my server.  

    SELECT CAST(733497 AS datetime) returns 3908-04-01 00:00:00.000
    LVL 4

    Assisted Solution

    On the SQL side you can use CONVERT(varchar(10), [DateColumn] ,101) to express the SQL date as a string in the form mm/dd/yyyy. See for more formats if needed.

    On the Crystal side you can use CDATE(string) to convert the string value from SQL back to a date value which you can then format using the standard field formatting features of Crystal. I typically use a formula field and base my report field on the formula field.

    Author Comment

    Okay, I see where you are both going but I may not be able to use a view in SQL each time so I may have to do it all from Crystal. When I try these in Crystal it doesn't get the correct date.
    Do you know how to convert it in Crystal?

    Author Comment

    I just got it to work. For anyone who needs this.
    DateAdd("d",({}-722815),Date (1980,01 ,01 ))
    On the Design tab in Crystal reports I right clicked the Formula field and chose New. Then you can paste in this formula. Just change {} to your date field.
    This date field is from an Epicor database and it may use a different type then most.
    See ID: 24117848 as well. Getting the rata die is discussed.
    I will award the points between you both. I think that is helpful information. My date is just different. If it helps the date we needed turned out to be March 31, 2009.
    Thanks again. Have a good day.

    Author Closing Comment


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now