Select data = to current date

[rwaldron]

Hi all, I am trying to do something that sounds easy but in fact is turning out to be very difficult.
I am trying to return data in DB2 where a field MTTRDT is equal to the current date..I know in DB2 I can use WHERE DB2 = CURRENT DATE........However my problem is that my MTTRDT field is not in date format..It is numeric and is yyyymmdd....I have managed to use the SUBSTR command to convert this to a proper date ie yyyymmdd to yyyy-mm-dd.
But I still cannot get this to only return data where MTTRDT (which is now yyyy-mm-dd) = Current Date..
The code below is close, even setting NULL if MTTRDT has a 0 but it falls over saying not vaild data type. Is there any way to get this to work so that I can have a rolling Day query....Thx Ray..
My Curent code is

'SELECT CASE WHEN MTTRDT = 0 THEN null ELSE DATE((SUBSTR(CHAR(MTTRDT),1,4) ||''-''|| SUBSTR(CHAR(MTTRDT),5,2) ||''-''|| SUBSTR(CHAR(MTTRDT),7,2))) END MTTRDT FROM MVXADTA.ODLINE
WHERE DATE((SUBSTR(CHAR(MTTRDT),1,4) ||''-''|| SUBSTR(CHAR(MTTRDT),5,2) ||''-''|| SUBSTR(CHAR(MTTRDT),7,2))) >= (current date) ')

 

[blom0344]

The DB2 CHAR function has the nasty habit of adding leading zeroes.

In any case check how the expression

(SUBSTR(CHAR(MTTRDT),1,4) ||''-''|| SUBSTR(CHAR(MTTRDT),5,2) ||''-''|| SUBSTR(CHAR(MTTRDT),7,2)) is returned and whether it fits the bill..

The SQL cookbooks (search DB2 Birchall) give some example of using UDF's to define proper conversion of numeric to string.

Ties Blom

 

[rwaldron]

Hiya , the Date returned is 2007-11-15 00:00:00.000

how can I strip this down to just 2007-11-15 ?

Thx,

Ray..

 

[blom0344]

The value you mention is of the type 'timestamp'.
Try applying an additional DATE on the expression , or try and wrap the DAYS function on both sides of the equation:

SELECT CASE WHEN MTTRDT = 0 THEN null  ELSE DATE((SUBSTR(CHAR(MTTRDT),1,4) ||''-''|| SUBSTR(CHAR(MTTRDT),5,2) ||''-''|| SUBSTR(CHAR(MTTRDT),7,2))) END MTTRDT  FROM MVXADTA.ODLINE 
WHERE DAYS(DATE((SUBSTR(CHAR(MTTRDT),1,4) ||''-''|| SUBSTR(CHAR(MTTRDT),5,2) ||''-''|| SUBSTR(CHAR(MTTRDT),7,2)))) >= DAYS(current date))

Ties Blom

 

[MarcLodge]

Ray,
I'm surprised that you have managed to get a timestamp from a date or numerical field, which I can only imagine is an installation default.

Could you check the DCLGEN format of the field and get back to us. Also could you run

SELECT MTTRDT
, CHAR(MTTRDT)
, SUBSTR(CHAR(MTTRDT),1,4)
, SUBSTR(CHAR(MTTRDT),5,2)
, SUBSTR(CHAR(MTTRDT),7,2)
FROM MVXADTA.ODLINE

and let us know the results