CODEWITHSUNDEEP
mysqlsqldb2
Geoff_S
Geoff_S

Reputation: 5107

CURDATE functionality from db2 query

I'm trying to apply 'curdate()' functionality to a select statement from DB2. I'm used to MySQL but I'm still trying to get the hang of a lot of the DB2 functionality and how to essentially marry the two.

My query is complete except for one line. I'm trying to select based on a ship date, which is the column EXTD1H and I need to check it against today or curdate(). The problem is that column in DB2 is an integer format, not a date format, and I don't have the option of changing it. In prior inserts to mysql, I've been able to put it into Y-m-d format and I know I can trim the year using LEFT(EXTD1H, 4) but I have no idea how to modify my select so that I can say WHERE EXTD1H is today so that I'm only selecting records for this date.

Here's the query:

select 
  invnoz as ORDER,
  fstatz as STATUS 
from gportafl
  /*where EXTD1H is curdate, hypothetically*/ 
  AND FSTATZ <> 'S'
  limit 20;

As you can see, I have a commented line where my issue is. I'm sure it's simple I just can't seem to find in the documentation exactly what I'm looking for, which is to be able to use that INT column to verify that selected records are from today.

UPDATE:

All values from the column are in YYYYMMDD format i.e. 20180202 but it should be 2018-02-02

Upvotes: 1

Views: 583

Answers (1)

Stavr00
Stavr00

Reputation: 3314

It's best not to do operations on the columns, so the indexes are used. You can typecast the current date to fit your data as follows:

WHERE extd1h = INTEGER(VARCHAR_FORMAT(CURRENT DATE,'YYYYMMDD'))

Upvotes: 2

Related Questions