How do I modify my Crystal Reports SQL to pull yesterdays date?

in my main record selection formula I have

 {my_table.my_date_field} in currentdate - 1 ;

When I look at the SQL in generates it hard codes this date as:

 ("my_table"."my_date_field">={ts '2010-04-19 00:00:00'} AND "my_table"."my_date_field">={ts '2010-04-20 00:00:00'})

I need this SQL statement to transplant into a second report as a command but I need the dynamic function of using the yesterday date.
Kanman123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UnifiedISCommented:
Use Today - 1
Kanman123Author Commented:
Use Today -1 in the SQL?  So my SQL would look like this?

 ("my_table"."my_date_field">= today -1 AND "my_table"."my_date_field"<Today)

UnifiedISCommented:
No, sorry, Today is a crystal function.

The conversion will remove any time values and subtracting one day gets the front end of the range as yesterday beginning of the day.  
GETDATE() returns the current date/time from your SQL server

 (
"my_table"."my_date_field">= CONVERT(varchar(10), DATEADD(dd, -1, GETDATE()), 101)
AND
"my_table"."my_date_field"<CONVERT(varchar(10), GETDATE(), 101)
)
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

mlmccCommented:
What was wrong with your first idea?

It seems to be working since I assume you have >= instead of < in the second comparison.

mlmcc
Kanman123Author Commented:
The today function seems to compile in crystal SQL.  The syntax below gives me data for both yesterday and today.


("my_table"."my_date_field">={ts 'TODAY -1 00:00:00'} )

What I want data for yesterday only.  The following does not give me any data at all but I can't understand why.


("my_table"."my_date_field" >={ts 'TODAY -1 00:00:00'} AND "("my_table"."my_date_field"<{ts 'TODAY 00:00:00'})




AND "cred_status"."update_dt"<{ts 'TODAY 00:00:00'})
mlmccCommented:
Does this alone give you all the data for yesterday?
("my_table"."my_date_field" >={ts 'TODAY -1 00:00:00'} AND "("my_table"."my_date_field"<{ts 'TODAY 00:00:00'})

Are you doing this in the database or in a Crystal COMMAND?

mlmcc
UnifiedISCommented:
Can you show the SQL query that Crystal generates when you use the one that doesn't return any data?

("my_table"."my_date_field" >={ts 'TODAY -1 00:00:00'} AND "("my_table"."my_date_field"<{ts 'TODAY 00:00:00'})


Another way to do this is with date parts.  Compare the year, month and day values.
In SQL
WHERE...
YEAR(my_date_field) = YEAR(DATEADD(dd, -1, GETDATE()))
AND MONTH(my_date_field) = MONTH(DATEADD(dd, -1, GETDATE()))
AND DAY(my_date_field) = DAY(DATEADD(dd, -1, GETDATE()))

Crystal syntax is similar but you can use Today - 1 in place of DATEADD(dd, -1, GETDATE())
Kanman123Author Commented:
This is the SQL that my first report generates.

SELECT distinct "my_table"."my_field", "my_table"."my_date_field",

FROM "my_table"

WHERE ("my_table"."my_date_field">={ts '2010-04-25 00:00:00'} AND
"my_table"."my_date_field"<{ts '2010-04-26 00:00:00'})
 

Let's say this is run on Feb 26th.

The Crystal Syntax I used on the report that generates this SQL is:

{my_table.my_date_field} in today - 1 ;

I need to run this Crystal generated SQL in a second report but I need the SQL to not hard code the date. I just want to select the date from "yesterday".

 Ironically ("my_table"."my_date_field">={ts 'TODAY -1 00:00:00'} )

will give me records all on its own.  When I add the second part which is

AND "my_table"."my_date_field"<TODAY) which defaults in zero records.




Kanman123Author Commented:
Points boosted to 250.
Kanman123Author Commented:
Points boosted to 350
UnifiedISCommented:
I don't understand what this means: I need to run this Crystal generated SQL in a second report but I need the SQL to not hard code the date.


Your where clause would include this to get yesterday using SQL:
WHERE  (
"my_table"."my_date_field">= CONVERT(varchar(10), DATEADD(dd, -1, GETDATE()), 101)
AND
"my_table"."my_date_field"<CONVERT(varchar(10), GETDATE(), 101)
)
Kanman123Author Commented:
My database is Postgre and this syntax will not compile.
Kanman123Author Commented:
Boosted to 500
UnifiedISCommented:
It would have helped you to include this in the Postgre zone.  I don't know Postgre SQL.  The above will work with MS SQL.  Maybe you could try to find the comparable functions and syntax for Postgre.

I added Postgre as a tag to this question.  I'm not sure if you can add it to another zone now but if you can, I would recommend that over boosting the points.
UnifiedISCommented:
I requested the zone addition for you
Kanman123Author Commented:
I found the right syntax.

("my_table"."my_date_field">= 'YESTERDAY'
 and "my_table"."my_date_field"<'today')

http://www.postgresql.org/docs/8.2/static/datatype-datetime.html

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.