asked on

String to date conversion in Crystal Reports

I am using Crystal Version 11. I have a 14 character date-time string. ex: 20110922181577. I am trying to pull the date out. I created a formula field to get the first 8 characters.
Left({TABLE.DATETIME},8).
This did get me 20110922. I called it DateOnly.

Now I want to use a SQL Expression field to make it a true date because I need to be able to pull data for the previous month. I tried (several iterations of the following)

(fn(Convert (String
(Left("DateOnly",4),
'-',
Right(Left("DateOnly",6),2),
'-',
Right("DateOnly",2)
)
, SQL_DATE
))

This being said, I am guessing that maybe I don't even have to use the Formula Field and this can be done directly from the original field {table.datetime}.

Also, is there a function in Crystal the allows you to query for the previous month's data without having to put in the date each time since this will be a scheduled report?

Thank you,
LindaOKState

BlueYonder

CDate (
(ToNumber ({TextDateField} [1 to 4])),
(ToNumber ({TextDateField} [5 to 6])),
(ToNumber ({TextDateField} [7 to 8]))
)

Kurt Reinhardt

Actually, you can't use the formula within your SQL Expression. SQL Expressions only allow the use of true database fields and they're evaluated before formulas during the multi-pass process. You'll have to do the true conversion with pure SQL. Personally, I'd use something like this:

CAST(SUBSTRING("TABLE"."FIELD",5,2)+'/'+SUBSTRING("TABLE"."FIELD",7,2)+'/'+LEFT("TABLE"."FIELD",4) AS DATE)

Open in new window

Please note the format of the database fields. You'll need to add the database fields directly from the field tree in the SQL Expression Editor.

Regarding the function question you can use LastFullMonth. So, if you have a SQL Expression called {%DateOnly} then your selection criteria would be:

{%DateOnly} in LastFullMonth

Kurt Reinhardt

The problem with the CDate conversion is that it's a formula, not a SQL Expression. If you use it in your record selection criteria then it won't pass the criteria to the database server for processing. That means you'll literally pull back every single possible record across the network only to filter it after the fact on the client. When you use a SQL Expression field, you can actually process the filter on the database server.

SOLUTION

BlueYonder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kurt Reinhardt

Yes, but how did you get '20120710'? At minimum, you'd need to use a LEFT against the actual database field, so it might look like this:

CONVERT(DATE,LEFT("TABLE.FIELD",8)  112)

Open in new window

SOLUTION

Kurt Reinhardt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LindaOKSTATE

ASKER

rhinok- both of these give error that DATE is not defined system type, also what is the 112?

CONVERT(DATE,LEFT("TABLE"."FIELD",8) ,112) (in the sql expression)

CAST(SUBSTRING("TABLE"."FIELD",5,2)+'/'+SUBSTRING("TABLE"."FIELD",7,2)+'/'+LEFT("TABLE"."FIELD",4) AS DATE (in the sql expression)

BlueYonder this gives no errors in the formula field but I think it looks like we should use the SQL instead?
CDate (
(ToNumber ({TextDateField} [1 to 4])),
(ToNumber ({TextDateField} [5 to 6])),
(ToNumber ({TextDateField} [7 to 8]))
)

Mike McCracken

Do you have a date field in the recordset or just this character field?

mlmcc

LindaOKSTATE

ASKER

There are just string fields in the entire table.

Kurt Reinhardt

What SQL Database are you using? I thought SQL Server, but if it's Oracle or something else, the syntax and data types will be different. Try using datetime instead of date. Also, if you're using Oracle, LEFT won't work. You'll need to use the SUBSTR function instead. Also, depending on the database, you may need to format the date in a different order.

The 112 is a style for SQL server that determines the date format.

LindaOKSTATE

ASKER

Well, you have made me reconsider this. I am writing reports for the same system as my former client. I have just started and not really sure of all the details. But I realized that they take the table out of the database I am used to (Cache) and pop it into another database. Is there any way to tell what type it is? I did find this in another report

'This formula is used to format the parameter date range values in the page header
formula = totext(Cdate({@DateFrom}),"MM/dd/yyyy")+" - "+totext(cdate({@DateTo}),"MM/dd/yyyy")

Is there a way to tell from this what type of database it is? I will have to wait until I can contact the client next week if we cannot figure it out.

Thanks.

Kurt Reinhardt

There's no way to what database that is, because it's just standard Crystal and Basic Syntax. You can, however, go to Database|Set Location and drill down through the properties to see if you can see the database type there.

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LindaOKSTATE

ASKER

James, I did try this and it looks promising. However, as I said this is a new client and I am still figuring out where all their data is stored. When I ran this all the records that returned were false, there were no true records. When I just ran an search on all those strings representing dates, there were only some for last year. Obviously, I need more info from the client. So I am getting with the client next week and will try to run this when I find where they are putting the correct data. I will get back with you. Thanks.

For everyone else helping me, if I get to run these reports off the actual database (like I used to) I may be able to use some of your suggestions also. I thank you all. Be back next week.

LindaOKState