| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-19-11, 12:35
|
|
Registered User
|
|
Join Date: Jul 2003
Location: Los Angeles
Posts: 12
|
|
|
Unable to convert string to timestamp
|
|
We have a staging table that's used to load raw data from our suppliers.
One column is used to capture a time-stamp but its data-type is varchar(265). Data's dirty: about 40% of the time, there is garbage data, otherwise time-stamp data like this
Code:
2011/11/15 20:58:48.041
I have to create a report that filters some dates/timestamps out that column but where I try to cast it, I get an error:
Code:
db2 => select cast(loadedon as timestamp) from automation
1
--------------------------
SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
Is there a way around that? Can you help? Thanks in advance.
|
|
12-19-11, 13:27
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
I think you have two options: write a stored procedure and handle rows in a loop, catching the exception, or create your own function to convert strings to timestamps while ignoring "garbage".
|
|
12-20-11, 23:03
|
|
Registered User
|
|
Join Date: Dec 2011
Posts: 2
|
|
|
|
you try this.
date(to_date(substr(START_TIME,1,8),'yyyymmdd'))
|
|
12-21-11, 02:20
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
If the format of valid strings was only 'yyyy/mm/dd hh:mi:ss.fff',
it might be enough to replace slash("/") to hyphen("-") then to convert it to timestamp.
Here is an example to convert varchar data with format 'yyyy/mm/dd hh:mi:ss.fff' to timestamp
and data with other than the format to null.
Example 1:
Code:
------------------------------ Commands Entered ------------------------------
WITH
automation(loadedon) AS (
VALUES
'2011/11/15 20:58:48.041'
, 'abcdefg'
, '2011/11/15*20:58:48.041'
, '20*1/11/15 20:58:48.041'
, '****/**/** **:**:**.***'
, ' / / : : . '
, '2011-11-15 20:58:48.041'
, '2011/11/15 20: 8:48.041'
, ' 2011/11/15 20:58:48.041'
, '2011/13/15 20:58:48.041'
, '2011/11/00 20:58:48.041'
, '2011/02/28 20:58:48.041'
, '2011/02/29 20:58:48.041'
, '2012/02/28 20:58:48.041'
, '2012/02/29 20:58:48.041'
, '2000/02/28 20:58:48.041'
, '2000/02/29 20:58:48.041'
, '2011/11/15 24:00:00.041'
, '2011/11/15 20:60:48.041'
, '2011/11/15 20:58:60.041'
, '2011/11/15 24:00:00.000'
)
SELECT loadedon
, CASE
WHEN TRANSLATE(loadedon , '+' , '*0123456789' , '*')
= '****/**/** **:**:**.***'
AND SUBSTR(loadedon , 1 , 4) BETWEEN '0001' AND '9999'
AND ( SUBSTR(loadedon , 6 , 2) IN ('01' , '03' , '05' , '07' , '08' , '10' , '12')
AND SUBSTR(loadedon , 9 , 2) BETWEEN '01' AND '31'
OR SUBSTR(loadedon , 6 , 2) IN ('04' , '06' , '09' , '11')
AND SUBSTR(loadedon , 9 , 2) BETWEEN '01' AND '30'
OR SUBSTR(loadedon , 6 , 2) = '02'
AND SUBSTR(loadedon , 9 , 2) BETWEEN '01' AND '28'
OR SUBSTR(loadedon , 6 , 2) = '02'
AND SUBSTR(loadedon , 3 , 2) IN ( '00' , '04' , '08' , '12' , '16'
, '20' , '24' , '28' , '32' , '36'
, '40' , '44' , '48' , '52' , '56'
, '60' , '64' , '68' , '72' , '76'
, '80' , '84' , '88' , '92' , '96'
)
AND SUBSTR(loadedon , 1 , 4) NOT IN ('1600' , '2000' , '2400')
AND SUBSTR(loadedon , 9 , 2) = '29'
)
AND ( SUBSTR(loadedon , 12 , 2) <= '23'
AND SUBSTR(loadedon , 15 , 2) <= '59'
AND SUBSTR(loadedon , 18 , 2) <= '59'
OR SUBSTR(loadedon , 12 , 12) = '24:00:00.000'
)
THEN
CAST( REPLACE(loadedon , '/' , '-') AS TIMESTAMP )
END AS valid_timestamp_value
FROM automation
;
------------------------------------------------------------------------------
LOADEDON VALID_TIMESTAMP_VALUE
------------------------- --------------------------
2011/11/15 20:58:48.041 2011-11-15-20.58.48.041000
abcdefg -
2011/11/15*20:58:48.041 -
20*1/11/15 20:58:48.041 -
****/**/** **:**:**.*** -
/ / : : . -
2011-11-15 20:58:48.041 -
2011/11/15 20: 8:48.041 -
2011/11/15 20:58:48.041 -
2011/13/15 20:58:48.041 -
2011/11/00 20:58:48.041 -
2011/02/28 20:58:48.041 2011-02-28-20.58.48.041000
2011/02/29 20:58:48.041 -
2012/02/28 20:58:48.041 2012-02-28-20.58.48.041000
2012/02/29 20:58:48.041 2012-02-29-20.58.48.041000
2000/02/28 20:58:48.041 2000-02-28-20.58.48.041000
2000/02/29 20:58:48.041 -
2011/11/15 24:00:00.041 -
2011/11/15 20:60:48.041 -
2011/11/15 20:58:60.041 -
2011/11/15 24:00:00.000 2011-11-15-24.00.00.000000
21 record(s) selected.
|
Last edited by tonkuma; 12-21-11 at 02:27.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Unable to convert string to timestamp
