DB2 : the syntax of the string representation of a datetime value is incorrect

DB2 query to get data between two DATE range ie to get data between start date and end date. Basically comparism of dates in DB2.

while writing a SQL query i was getting below mentioned error.


the syntax of the string representation of a datetime value is incorrect

Correct Syntax : 

select OPRID,DATETIME_STAMP,H_SS_ID from Q.PROJECT where date(DATETIME_STAMP) between '2011-08-04' and '2011-08-06' order by DATETIME_STAMP

here it will get data from date range between  2011-08-04 and 2011-08-06. Note date syntax (YYYY-MM-DD)

Also note the date function DATE(..) it will convert complete date to a YYYY-MM-DD format.

 If you want data of one particular day

select OPRID,DATETIME_STAMP,H_SS_ID from Q.PROJECT where date(DATETIME_STAMP) = '2011-08-04'

The basics of DATE TIME in DB2

To get the current date, time, and timestamp using SQL, reference the appropriate DB2 registers:

SELECT current date FROM sysibm.sysdummy1 
SELECT current time FROM sysibm.sysdummy1 
SELECT current timestamp FROM sysibm.sysdummy1 

The sysibm.sysdummy1 table is a special in-memory table that can be used to discover the value of DB2 registers. 

Given a date, time, or timestamp, you can extract (where applicable) the year, month, day, hour, minutes, seconds, and microseconds portions independently using the appropriate function:

YEAR (current timestamp) 
MONTH (current timestamp) 
DAY (current timestamp) 
HOUR (current timestamp) 
MINUTE (current timestamp) 
SECOND (current timestamp) 
MICROSECOND (current timestamp) 

Extracting the date and time independently from a timestamp is also very easy:

DATE (current timestamp) 
TIME (current timestamp) 

You can also perform date and time calculations using, for lack of a better term, English: 

current date + 1 YEAR 
current date + 3 YEARS + 2 MONTHS + 15 DAYS 
current time + 5 HOURS - 3 MINUTES + 10 SECONDS 
 

To calculate how many days there are between two dates, you can subtract dates as in the following:

days (current date) - days (date('1999-10-22'))