SimonSaysSQL
asked on
Convert Date in Crystal Reports and or in a SQL View
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
An example of the date is 733497.
I am using Crystal XI if this helps.
Thanks for your time.
Wendy
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.
How do you want the date to appear in your report?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
Do you know how to convert it in Crystal?
ASKER
I just got it to work. For anyone who needs this.
DateAdd("d",({gl.date}-722 815),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.
DateAdd("d",({gl.date}-722
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.
ASKER
Thanks.