asked on

Select data from table based on current date

Hello EE.

I'm trying to run a sql statement is DB2 to use as part of a stored procedure but it doesn't work.

select * from table1 where created_date = date(char(current timestamp)) - 1 days

I'm getting this error:
SQL0401N The data types of the operands for the operation "="
are not compatible. SQLSTATE=42818

I need to select data based on the prior day(which I was trying to do above), last 7 days and last full month. All of the date fields in my tables are of datetime format and I only need to evaluate the date. Any suggestions???

Thanks!

sachinwadhwa

Try this:

select * from table1 where created_date = date(current timestamp - 1 day)

sachinwadhwa

sorry, if created_date is datetime/timestamp, try this:

select * from table1 where created_date = (current timestamp - 1 day)

mikmik74

ASKER

That works, but I don't get any records back because it's looking for that exact timestamp. I don't want it to consider the time (hours, seconds)...I only want it to consider the actual date 3/22/06.

ASKER CERTIFIED SOLUTION

sachinwadhwa

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Member_2_2484401

If created_date has a data-type of DATE (as its name suggests), I'd use current date instead of current timestamp.

e.g.
select * from table1 where created_date = current date - 1 day

HTH,
DaveSlash

mikmik74

ASKER

DaveSlash,
The created_date date type is datetime.

sachinwadhwa,
I don't know why I didn't think of that??? But, yes that worked. So I'd use the following:

Prior Day
select * from table1 where date(created_date) = date(current timestamp - 1 day)

Last 7 days
select * from table1 where date(created_date) = date(current timestamp - 7 day)

Last Month
select * from table1 where month(created_date) = Month(current timestamp - 1 month) and Year(created_date) = Year(current timestamp - 1 month)

Thanks!