|
View previous topic :: View next topic
|
| Author |
Message |
arultm
New User
Joined: 03 Dec 2007
Posts: 7
Location: Mumbai
|
|
|
|
Hi,
Can we convert date in timestamp of type '2007-12-06-00.00.00.000000' in the table into numeric field like 20071206 and can we use it in WHERE clause for arithmetic comparision?
If so please inform how to do it? |
|
| Back to top |
|
 |
shankar.v
Active User
Joined: 25 Jun 2007
Posts: 196
Location: Bangalore
|
|
|
|
arultm,
| Quote: |
| can we use it in WHERE clause for arithmetic comparision? |
Can you please brief on your requirement with an example regarding what type of arithmetic comparision required?
Please check with the below code which retrieve the DATE in TIMESTAMP as INT(numeric) field.
| Code: |
---------+---------+---------+---------+---------+---------+----
SELECT INT(SUBSTR(CHAR(CURRENT_TIMESTAMP),1,4)||
SUBSTR(CHAR(CURRENT_TIMESTAMP),6,2)||
SUBSTR(CHAR(CURRENT_TIMESTAMP),9,2)) AS NUMERIC_DATE
FROM SYSIBM.SYSDUMMY1;
---------+---------+---------+---------+---------+---------+----
NUMERIC_DATE
---------+---------+---------+---------+---------+---------+----
20071206
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---- |
|
|
| Back to top |
|
|
arultm
New User
Joined: 03 Dec 2007
Posts: 7
Location: Mumbai
|
|
|
|
Hi,
I have to fetch rows from the table with date starting from 30/06/2005 to till now(today's date).
Each row has timestamp value which is the time of entry for that row in to the table.
Can I give
SELECT *
FROM tablename
WHERE INT(SUBSTR(CHAR(CURRENT_TIMESTAMP),1,4) > 2005
............... So on
directly...
Will it take more time to fetch values? |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
From there it indicates you could do the following, getting rid of that int/substr garbage. By doing that you ensure that your app is not portable(if that is a concern) because the default date format can change between databases.
| Code: |
SELECT *
FROM tablename
WHERE YEAR(CURRENT_TIMESTAMP) > 2005
|
|
|
| Back to top |
|
|
shankar.v
Active User
Joined: 25 Jun 2007
Posts: 196
Location: Bangalore
|
|
|
|
arultm,
| Quote: |
I have to fetch rows from the table with date starting from 30/06/2005 to till now(today's date).
Each row has timestamp value which is the time of entry for that row in to the table. |
Please check with the below query for your requirement.
| Code: |
SELECT * FROM TABLE_NAME
WHERE DATE(COLUMN_TIMESTAMP)>='2005-06-30'
AND DATE(COLUMN_TIMESTAMP)<=CURRENT_DATE; |
|
|
| Back to top |
|
|
|
|
