SQL Query Begin of Previous Month

[former_member1234710]

I have a report written in CR 2011 that I want to automate. In the SQL Query Command I have:

TICKHISD."DtTransDate" >= {d'2020-04-01'} and

I am basically pulling everything from the start of the previous month.

What do I need to do to not have the actual date in the query?

[venkateswaran_k]

Dear Chris,

Then use the query as below

WHERE
TICKHISD."DtTransDate" >= (
       SELECT ADD_MONTHS(NEXT_DAY(LAST_DAY(CURRENT_DATE)),-1) FROM DUMMY; );

Regards,

Venkat

[former_member1234710]

Venkat;

The command did not work. I tried different variations with no luck. The report is connecting to a Pervasive database.

Chris

[former_member1234710]

Dell, also I'm only selecting records from the previous month. Not the start of the previous month to the current date.

[former_member1234710]

I'm using a command. The database is a Pervasive database.

[DellSC]

This depends on whether you're using a command or joining tables together.

If you're joining tables together, you can use Venkat's formula in the Select Expert.

However, if you're using a command, you can't use a Crystal formula. Instead you'll have to calculate the start date based on the current date using your database's syntax. If you'll let us know what type of database you're using, I should be able to provide that for you.

-Dell

[former_member1234710]

Dell;

I'm using a command and the database is a Pervasive database.

Also I'm only selecting records from the previous month. Not the start of the previous month to the current date.

[DellSC]

When working with your command, be sure to follow the best practices outlined here. In the Where clause of your command, you'll do something like this:

TICKHISD."DtTransDate" >= DateAdd(month, -1, DateAdd(day, -1*Day(Curdate())+1, Curdate()) and
TICKHISD."DtTransDate" <= DateAdd(day, -1*Date(Curdate()), Curdate())

I don't have access to a Pervasive database and I haven't really worked with it, but I looked up the the syntax. The basic logic for the dates is:

End Date: Subtract the number of the current day from the current date to get the last day of the previous month.

Start Date: Subtract the number of the current day from the current date, add one, then subtract 1 month to get the start day of the previous month.

-Dell

[venkateswaran_k]

Dear Chris,

Create a formula for startDate as

date(year(currentdate),month(currentdate)-1,1)

Use that variable in the Query

TICKHISD."DtTransDate" >= {startDate}

Regards,

Venkat

[former_member1234710]

I'm not using Select Expert, I'm using a Command.