SQL Selection In Crystal

[Bunting]

First off, if I've picked the wrong forum please advise - I wasn't sure which SQL forum would be best as I have no idea which SQL Crystal uses.

Now, I have a date field in my database (ODBC) stored as text "YYYYMMDD". I want to create a command in Crystal select records in SQL where the string represents yesterday. I though I had it with

WHERE  "Data"."Date_Field"=STR(DATEADD(dd,-1,TODAY),'yyyymmdd')

but apparently SQL doesn't like me trying to use the constant TODAY in a function.

Can you advise on how best to change my WHERE line to get the selection I want?

C

 

[Madawc]

In Crystal, you could do the following in record selection:

Data.Date_Field = currentdate-1

That's assuming the field is accepted as a date. Otherwise use Cdate to convert it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Bunting]

Thanks Madawc, but for the report in question I really need to create a command so the selection needs to be in SQL.

C

 

[bdreed35]

Bunting:

Crystal will allow you to use any valid SQL that your database will accept.
So if you are using SQL Server, and you have written a SQL statement in Query Analyzer that returns the data you want, then you can copy and paste that SQL into the SQL Command in Crystal.
Same goes with other databases and their query tools.

If you want help writing the actual SQL, post what database you are working with as that will effect what functions are available.

~Brian

 

[Bunting]

Hi Brian,

I tried the method you mentioned but no matter how I set it up the SQL appears with the date value fixed depending on the date when I refresh the report.

What information do you need on the database for help with the function?

C

 

[Turkbear]

Hi,
what exactly does this mean?:
date value fixed depending on the date when I refresh the report.
TODAY ( and its similar Crystal 'currentdate' and Oracle's 'sysdate' )
is not a constant but will return the current date - that is, the date when the report is run/refreshed..Do you mean that it 'stays' the Date when designed?


Again, as Brian asked, what database and version ( SQL is used in many,many different ones).






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Bunting]

Sorry, bad grammar. But the point is, if I use TODAY in the function DATEADD I get an error message.

On the other point, what I mean is that I can use a number of methods in the select expert e.g.
Data.Date_Field = CStr(CurrentDate - 1,"yyyyMMdd")
but when I click "Show SQL Query" Crystal refreshes the report and gives me the statement with the right side of this statement as a constant string, i.e. (for the example of running the report today)
WHERE "Data"."Date_Field" = '20060627'

I want to replace '20060627' in the SQL query with a function on TODAY to obtain the string for yesterday which will automatically update depending on when I run the report.

We're using SQL Server version 7.

 

[lbass]

If you use currentdate as in your last post, the SQL query will automatically be updated any time the report is run. If you run in tomorrow, the date in the Show SQL Query will read: '20060628'. It is just translating your formula.

-LB

 

[Bunting]

Got it sorted from another source - now using
WHERE "Data"."Date_Field"=convert(varchar(8),dateadd(day,-1,getdate()),112)
in the query for a command to create a new table selecting from the string date field as required.

Thanks for all of the suggestions.