CODEWITHSUNDEEP
db2
Agent Mahone
Agent Mahone

Reputation: 307

To Pull records between two dates in db2

I tried below two ways they not working 

Select * from Table 
where SERV_DATE BETWEEN '03/01/2013'AND    
              '03/31/2013'

ALSO This is not working 

Select * from Table 
where SERV_DATE BETWEEN DATE('03/01/2013') AND     
              DATE('03/31/2013')

What should be the correct format ?

Upvotes: 2

Views: 58721

Answers (5)

suresh kumar korrai
suresh kumar korrai

Reputation: 1

select count(*) from   TABLE where time_stamp BETWEEN DATE('2018-01-01') AND DATE('2018-01-31');

Here time_stamp is field name and copy your timestamp filed name instead of time_stamp.

Upvotes: -1

Vinit Bhardwaj
Vinit Bhardwaj

Reputation: 201

SELECT * FROM tableName WHERE date(modifiedBy_date) between '2017-07-28' AND '2017-08-01';

Works cool for DB2.

Upvotes: 4

Ramiro Alarcón
Ramiro Alarcón

Reputation: 19

Select * from Table 
where SERV_DATE BETWEEN DATE('2013-03-01') AND DATE('2013-03-31');

Worked for me.

Upvotes: 1

Raymond San Juan
Raymond San Juan

Reputation: 11

Select * from Table 
where (SERV_DATE BETWEEN '03/01/2013'AND    
              '03/31/2013') 


Select * from Table 
where (SERV_DATE BETWEEN '2013-03-01'AND    
              '2013-03-31')

Upvotes: 0

Peter Schuetze
Peter Schuetze

Reputation: 16305

Did you tried what NealB suggested? The reason for not accepting 03/01/2013 as an entry date format is, that it is region dependent in the US it is March 1, 2013 an in the UK it is January 3, 2013. So without considering the local, it is not certain, what the actual date is.

"why would db2 give error on the same format and will go well when given different format" - Don't forget, that db2 is an old lady and as all old ladies she has peculiarities. You just get used to it and there will be an happy ending.

Upvotes: 3

Related Questions