rashmirgowda
New User
Joined: 18 Jun 2008 Posts: 25 Location: india
|
|
|
|
Hi,
I am having a table TABLE A, one of the column is timestamp.I want to select records based on the date which is present in timestamp.I will be passing only date as input to the query.
select *
from TABLE A
where DATE(timestamp column) = '2010-06-21'
I have used this above query and not able to select the records.Please help in selecting the records. |
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
well, putting a scalar function on every row is not very efficient.
i would instead:
where timestamp_column between timestamp('2010-06-21', '00.00.00') and
timestamp('2010-06-21', '23.59.59')
the above format may or may not work on your version of db2,
nor have I tested it, because I don't have the time.
This would be good experience for you.
suggest you work in spufi until you are able to generate accurate timestamps
SELECT timestamp('2010-06-21', '00.00.00')
, timestamp('2010-06-21', '23.59.59')
from sysibm.sysdummy1
until that works.
then substitute the proper format/syntax of the timestamp('2010-06-21', '00.00.00')
in your basic sql.
addressing your original question,
if you had an sqlcode of 0, that means you do not have any rows
containing a timestamp within the date.
if you had an sqlcode <> 0, then say so.
probably would be a good idea if you
SELECT *
from tablea
fetch first 10 rows only
to see what valid dates exists, then construct your select with timestamp
for an existing date. |
|