Solved

timestamp function in db2

Posted on 2009-07-13
4
3,378 Views
Last Modified: 2012-08-14
I found strange things when using timestamp funtion in writing a query for db2 sql that had the following clause in the sql statement
AND   timestamp('2009-06-01','00:00:00') <=  startdatetime AND   StartDateTime < timestamp(date('2009-06-30') + 1 day,'00:00:00')

same above statement when it was written as follows
AND startdatetime >= '2009-06-01-00.00.00' and startdatetime <= '2009-06-30-00.00.00' - produced different results  - first one produced more aggregate records for evaluation then the second - what differences that db2 does in using a timestamp fuction as against direct comparisons of datetime field? (startdatetime is defined as timestamp of length 10)
0
Comment
Question by:mahjag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 18

Expert Comment

by:daveslash
ID: 24843866

On one clause you have "less-than", but on the other clause you have "less-than-or-equal-to"

HTH,
DaveSlash


0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 24843951
Hi mahjag,

What Dave said.

Also, the format of the datetime is incorrect.  The value '2009-06-01-00.00.00'  should be '2009-06-01.00.00.00'.  Note that a period should separate the date and time values, not a hyphen.  An improperly formatted datetime will affect the comparison in an ugly way.


Good Luck,
Kent
0
 
LVL 18

Accepted Solution

by:
daveslash earned 125 total points
ID: 24844353

That's realy interesting.  ( see below )

On DB2 for i, this works:
 
select *
from   deleteme
where  aTS >= '2009-07-01-00.00.00'
  and  aTS <= '2009-07-31-00.00.00'
;
 
But, this doesn't:
 
select *
from   deleteme
where  aTS >= '2009-07-01.00.00.00'
  and  aTS <= '2009-07-31.00.00.00'
;
 
[SQL0180] Syntax of date, time, or timestamp value not valid.

Open in new window

0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 24845151
Hi Dave,

Wow.  I've had scads of queries fail due to badly formatted date, time and/or datetime values.  I've never seen the behavior where a timestamp was accepted in that format.

Of course, I'm an LUW kind of guy.   :)    (Where a full timestamp really needs microseconds ('.000000') appended after the time.)


Kent
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question