?
Solved

Convert Date in Crystal Reports and or in a SQL View

Posted on 2009-04-28
8
Medium Priority
?
624 Views
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.
Wendy
0
Comment
Question by:SimonSaysSQL
  • 4
  • 2
  • 2
8 Comments
 
LVL 4

Expert Comment

by:glebn
ID: 24254731
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.
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 24254807
How do you want the date to appear in your report?
0
 

Author Comment

by:SimonSaysSQL
ID: 24254837
glebn
I would still have to look up how to get the right syntax.
UnifiedlS
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 18

Accepted Solution

by:
UnifiedIS earned 1000 total points
ID: 24254894
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
0
 
LVL 4

Assisted Solution

by:glebn
glebn earned 1000 total points
ID: 24255282
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 http://msdn.microsoft.com/en-us/library/ms187928.aspx 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.
0
 

Author Comment

by:SimonSaysSQL
ID: 24255464
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?
0
 

Author Comment

by:SimonSaysSQL
ID: 24255700
I just got it to work. For anyone who needs this.
DateAdd("d",({gl.date}-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 {gl.date} 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.
0
 

Author Closing Comment

by:SimonSaysSQL
ID: 31575648
Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Viewers will learn how the fundamental information of how to create a table.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

609 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