Midrange News for the IBM i Community

Posted by: rpgprogrammer
Vancouver, BC
SQL with Timestamp Filtering
has no ratings.
Published: 30 Aug 2013
Revised: 03 Sep 2013 - 3832 days ago
Last viewed on: 01 Mar 2024 (4455 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL with Timestamp Filtering Published by: rpgprogrammer on 30 Aug 2013 view comments(2)

Need a expert advice, I want to find all records from a given file which falls between two timestamp example select * from filename where filefield.timz between '2013-08-31-' and '2013-08-31-'. Seems the timestamp values are not numeric so bit confused..

Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Comment on: SQL with Timestamp Filtering
Posted: 10 years 6 months 2 days 13 hours 38 minutes ago
Edited: Fri, 30 Aug, 2013 at 20:50:48 (3836 days ago)

You have them quoted, so what's the problem? Perhaps TIMESTAMP only goes to milliseconds--try omitting the last 3 zeros and see if it takes it.

Posted by: jjcllhn
Premium member *
Comment on: SQL with Timestamp Filtering
Posted: 10 years 5 months 29 days 10 hours 7 minutes ago
I'm a bit confused the way you reference column filefield in your example. Is it meant to imply that filefield is defined as a timestamp, or that the values of filefield should be interpreted as timestamp values, or is it something else I've failed to consider? It's not typical notation for DB2i. If the column is defined as a timestamp, no qualifier would be needed (What does .timz represent?), SQL will convert to constants to timestamp for comparison. If the column is defined as a numeric, or a character, you'd format the string and convert it to a timestamp value using the timestamp function, and your SELECT statement would look like this: SELECT * FROM filename WHERE TIMESTAMP( formatted_filefield ) BETWEEN ts_value1 AND ts_value2 FWIW, DB2i translates its internal representations of dates, times and timestamps into external values that are locale friendly based on format settings.