16

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?

burnellj
  • 23
  • 5
yons88
  • 439
  • 2
  • 5
  • 20
  • 3
    How is the "registrationdate" column defined? Maybe the column data type isn't a date, if it were, then your original query should have worked. – NealB May 28 '12 at 19:17

5 Answers5

22

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';
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
8

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

registrationdate > '2002-10-01'
yons88
  • 439
  • 2
  • 5
  • 20
4

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';

ultimatum
  • 89
  • 3
1

Use this format.

registrationdate > 'mm/dd/yyyy'
Dhanish Jose
  • 739
  • 1
  • 8
  • 19
  • Assuming that `registrationDate` _is_ a date type, changing the format will have no effect. If it's something like a char type, this **won't work AT ALL** - the format you've chosen doesn't sort nicely. ISO is the standard recommended format for a reason. – Clockwork-Muse Jan 03 '14 at 08:53
0

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

MOHAMMED ASIF KHAN
  • 3
  • 4