SQL Designer Date w/8.5 Dev 3

[SGTech]

Version: CR 8.5 Developers

The following select was entered using Crystal SQL
Designer:

select callid, calldate, activitydate from helpdesk.calllog where calldate <= '10/15/2001' and activitydate = '9/1/2001'
union
select callid, calldate, activitydate from helpdesk.calllog_hist where calldate <= '10/15/2001' and activitydate >= '9/1/2001'

Data was sucessfully retrieved in the SQL Designer.
(Actual dates will ultimately be supplied as paramaters.)


When a report is based on this query and grouped on 'activitydate', the option to 'Break: for each week' is not available. I need this function for charting purposes.

Any assistance greatly appreciated,
Stan

 

[Ngolem]

since your date fields seem to be strings ( '9/1/2001' )

I think you have to create a formula to convert them to a date value before you can use the other function.

if your date had 2 digits for Month and day and your date format is month/day/year , the conversion would be

date(tonumber({query.Activitydate}[7 to 10]),tonumber({query.Activitydate}[1 to 2]) ,tonumber({query.Activitydate}[4 to 5]))

hope this helps
Jim

 

[kenhamady]

QRY files store data (internally) in Dbase IV format, which will convert dateTime values to Strings. If you browse the field in CR you will probably see that it is a string. If you are getting a DateTime String you can use the function:

DTSToDateTime() to convert this value to a True DateTime value, which will have date grouping options. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SGTech]

Thanks for assisting a newbie(boy?) on the block!!

This line did the trick:

DTStoDate(Replace({Query.activitydate},&quot;-&quot;,&quot;/&quot)

With this help, I may be able to assist someone else in the future!

Thanks again for sharing.
Stan

 

[kenhamady]

I believe that if you use:

DTSToDateTime() instead of
DTSToDate()

you might not need to do the replace. This keeps the time value, but you can format that to not show. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SGTech]

Thanks for additional info. Stan