CODEWITHSUNDEEP
db2
yons88
yons88

Reputation: 459

Comparing DB2 dates

I have a DB2 DATE type field in a DB2 table. I want to select data by a date filter. For example: 

SELECT *
FROM   table
WHERE registrationdate > '2002-10-01';

From the above query, I get records with registrationdate starting from '1943-10-01', but this is incorrect.

These do not work either:

registrationdate > date('2002-10-01')
date(registrationdate) > date('2002-10-01')
date(registrationdate) > '2002-10-01'

How do I need to compare dates?

Upvotes: 16

Views: 118006

Answers (5)

MOHAMMED ASIF KHAN
MOHAMMED ASIF KHAN

Reputation: 3

In DB2 you can use 'LIKE' as well for DATE type.

For example if you want all data from a particular date.

select e.* from EMPLOYEES as e where b_date LIKE '1972-07-31';

This works without an error and outputs the data wrt the date.

Or Tuples from a decade will be as follow: ex: Records from the 1970s

select e.* from EMPLOYEES as e where b_date LIKE '197%';

Will return the record of 1970s.

Using Like for a specific date in DB2

Finding year using LIKE

Upvotes: 0

ultimatum
ultimatum

Reputation: 89

The default format used for dates is determined by the territory code of the DB2 database (which can be specified at database creation time). For example, my database was created using territory=US. Therefore the date format looks like the following:

values current date 
1 
---------- 
05/30/2003 

1 record(s) selected.

You may get the territory code from DB CFG.

db2 get db cfg | egrep 'code|territory'
 Database territory                                      = US
 Database code page                                      = 1208
 Database code set                                       = UTF-8
 Database country/region code                            = 1

That is, the format is MM/DD/YYYY. If you want to change the format, you can bind the collection of db2 utility packages to use a different date format.

To get the current date, time, and timestamp using SQL,(basically gives you an idea of what the date format is) reference the appropriate DB2 registers: The sysibm.sysdummy1 table is a special in-memory table that can be used to discover the value of DB2 registers 

db2 "SELECT current date FROM sysibm.sysdummy1 "

1
----------
06/02/2014

  1 record(s) selected.

The DATE function still works even if we leave out the quotes in the function, but the result is not correct:

db2 "SELECT date(2001-09-22) FROM sysibm.sysdummy1 "

1
----------
05/24/0006

  1 record(s) selected.

When the DATE function gets a character string as input, it assumes that it is valid character representation of a DB2 date, and converts it accordingly. By contrast, when the input is numeric, the function assumes that it represents the number of days minus one from the start of the current era (that is, 0001-01-01). In the above query the input was 2001-09-22, which equals (2001-9)-22, which equals 1970 days.

And if all the above are applicable, the below command should work fine for your issue.

SELECT * FROM table WHERE registrationdate > '10/01/2002';

Upvotes: 4

Dhanish Jose
Dhanish Jose

Reputation: 747

Use this format.

registrationdate > 'mm/dd/yyyy'

Upvotes: 2

yons88
yons88

Reputation: 459

I explored some other problems with database, after restart it started to work, so this works correctly now:

registrationdate > '2002-10-01'

Upvotes: 10

Jonathan Leffler
Jonathan Leffler

Reputation: 754010

The SQL standard format for a DATE literal is:

DATE '2002-10-01'

At the very least, it is worth trying:

SELECT *
  FROM table
 WHERE registrationdate > DATE '2002-10-01';

Upvotes: 26

Related Questions