SQL Current date (AS400)

I am trying to select the records from an AS400 database that have a current date (in format MMDDYY).

This is what I a开发者_如何学Pythonm planning to do:

SELECT * FROM tableName WHERE $DATE='030411'

I tried combinations of this but with no luck:

SELECT * FROM tableName WHERE $DATE='SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '')'

Any suggestions?

Try this:

SELECT * FROM tableName WHERE $DATE=
   substring(cast(current date as char(10)),6,2) || 
   substring(cast(current date as char(10)),9,2) || 
   substring(cast(current date as char(10)),3,2)

You can see what value this expression brings back like this:

select substring(cast(current date as char(10)),6,2) || 
   substring(cast(current date as char(10)),9,2) || 
   substring(cast(current date as char(10)),3,2)    
from sysibm.sysdummy1                                   

You have to use sysibm.sysdummy1 since SQL on an AS/400 (iSeries, System i, etc.) doesn't let you SELECT values out of thin air.

Also note that current date might bring the date back in a different format, depending on the SQL date format. This code is expecting it to be in *ISO format (YYYY-MM-DD).

Here are some SQL statements that I used to validate this routine.

create table dmclib.test2 ( $DATE decimal(6,0) ) ;

insert into dmclib.test2 values   
  (010111), (010211), (031011) ;

SELECT * FROM dmclib.test2                         
where $DATE =                                      
   substring(cast(current date as char(10)),6,2) ||
   substring(cast(current date as char(10)),9,2) ||
   substring(cast(current date as char(10)),3,2) ;

Here's what I got back:

....+...                        
  $DATE                         
 31,011                         
********  End of data  ******** 

insert( replace( char( current_date, usa ) , '/', '' ) , 5, 2, '')

The above generates the character string representation of the *USA format [i.e. MM/DD/YYYY] for the Current Date using the CHAR cast scalar function, then replaces the '/' character in the string MM/DD/YYYY with the null string to become MMDDYYYY using the REPLACE scalar function, and then inserts the null string starting as position five [i.e. the first digit of YYYY in MMDDYYYY] while removing two bytes of that string [i.e. the "insert" is actually replacing the fifth and sixth bytes, or per the arguments on the insert scalar from the fifth (5) for two (2) bytes, with the null string] using the INSERT scalar function, thus producing the desired result of the string MMDDYY [where the last two bytes YY are the 10**1 and 10**0 portions of the number denoted as YYYY in MM/DD/YYYY].

Regards, Chuck

030411 =

select substring(Replace(Replace(convert(varchar(10),GETDATE(), 101), '/', ''), '/', ''),1,4)+ substring(convert(varchar(4),year(GETDATE()),101),3,2)

Try the below steps..

1. STRSQL

2. TYPE YOUR QUERY

3. PRESS F4

4. PUT YOUR CURSOR IN FRONT OF "WHERE"

5. PRESS F4

6. PUT 1 IN FRONT OF YOUR APPROPRIATE DATE COLUMN NAME

7. HIT ENTER

8. IF YOUR COLUMN IS NUMERIC GIVE VALUE WITHOUT QUOTES E.G 1234 AND NOT '1234'

9. HIT ENTER

10. YOU SHOULD GET YOUR RESULT