Sql to DB2 Convert to Date

[rwaldron]

Hi all,
I have a linked sevrer from SQL to DB2.
I have a column in DB2 ( Called Dispatched ) that is in the form 200060125 ie: Todays Date.
However within DB2 the Datatype of this field is NOT a date.
It is an 8 Digit Numeric.

How to I convert this to a proper data data type ( Directly in DB2) or in SQL
so that I can run some queries comparing it to the Current date (Getdate) in sql.

I have also used "current date" in DB2 that does return a proper date to SQL but
the column I need to compare any date with is Dispatched

Any help Much appretiated

Hi all,
I have a linked sevrer from SQL to DB2.
I have a column in DB2 ( Called Dispatched ) that is in the form 200060125 ie: Todays Date.
However within DB2 the Datatype of this field is NOT a date.
It is an 8 Digit Numeric.

How to I convert this to a proper data data type ( Directly in DB2) or in SQL
so that I can run some queries comparing it to the Current date (Getdate) in sql.

I have also used "current date" in DB2 that does return a proper date to SQL but
the column I need to compare any date with is Dispatched

Any help Much appretiated

P.S Just another quick question....I use SQL Query Analyzer alot.
But how do I know what Datatypes are returned from a query in the analyser from a results view ??
This would help alot see how sql has treated certain datatypes by default.

Ray..

Ray..

 

[MarcLodge]

Ray,
You need to tell DB2 to convert the field to a date. I'm assuming that the field is a plain char 8 field. If sol then the following should do the trick:

SELECT DATE( SUBSTR(DISPATCHED,1,4)||
             '-'
             SUBSTR(DISPATCHED,5,2)||
             '-'
             SUBSTR(DISPATCHED,7,2) )

Hope this helps.

Marc

 

[rwaldron]

Thx for the reply Marc..
The cuurent format of dispatch in DB2 is Numeric.

Do I run this code from within DB2 ?

I ran the code but got the error

"Token SUBSTR was not valid. Valid tokens: ) ,."

Could you also tell me what the code is doing..
I think its Inserting '-' at set points
so 20060425 becomes 2006-04-25
Is this correct ?

Ray..

 

[MarcLodge]

Ray,
If your column is numeric, try this instead:

SELECT  DATE(SUBSTR(CHAR(DISPATCHED),1,4) 
             ||'-'||                    
             SUBSTR(CHAR(DISPATCHED),5,2) 
             ||'-'||                    
             SUBSTR(CHAR(DISPATCHED),7,2))

What this is doing is taking the number, turning it into a character string (the CHAR part does this), then picking out pieces of that string using the SUBSTR (substring) function. The first number gives the start position and the second the aount of characters you wish 'extracted'. The || bit is the concatenation character and this lumps everything together as one output field. The DATE() around it all tells DB2 to treat it as a date.

Wherever you ran it from to give you your previous error, is where you should be running it from as that seems to work!

Marc

 

[rwaldron]

Hiya Marc,
I tried the code for my existing tables.
The column containing 20060425 is called ZXDSDT
and this exists in MVXAMODSFC/MZIMHS

I ran the code below but got the error
Token ( was not valid. Valid tokens: , FROM INTO

Code is :

SELECT DATE(SUBSTR(CHAR(ZXDSDT),14)
||'-'||
SUBSTR(CHAR(zxdsdt),5,2)
||'-'||
SUBSTR(CHAR(zxdsdt),7,2)) from mvxamodsfc/mzimhs

Do I still have a typo error?

 

[MarcLodge]

Hi Ray,

Looks like you are missing a comma between the 1 and the 4 on the first substr.

Marc

 

[rwaldron]

Hiya Marc,
Sorry to be annoying you..
That comma was an error in my forum message .
I am still getting the error
Token ( was not valid. Valid tokens: , FROM INTO

For the code

SELECT DATE((SUBSTR(CHAR(zxdsdt),1,4)
||'-'||
SUBSTR(CHAR(zxdsdt),5,2)
||'-'||
SUBSTR(CHAR(zxdsdt),7,2))) from mvxamodsfc/mzimhs

 

[MarcLodge]

Ray,
Don't worry about being 'annoying', you're not. When things just don't seem to work, that's what these forums are for. I just hope that we can solve this one.

Have you cut and paste the SQL, as you now appear to have an extra bracket between the DATE and first SUBSTR!

Also, what platform are you running this on? The reason I ask is that I've not seen the convention of a '/' in the table name before. Whenever I've seen it, it's a '.'.

Let me know what's what.......

Marc

 

[rwaldron]

Hiya Marc...I got it working for the code above..
I just had a few typos..
I now get date returned as 26/04/2006

Thx for your help..
Any idea how to get this code to run within a linked server query from SQL to DB2 ie: an Openquery.

The code below should work but I get an error
Incorrect syntax near '/'.

SELECT *
FROM OPENQUERY(MOVEX_MVXAMODSFC, 'SELECT DATE((SUBSTR(CHAR(zxdsdt),1,4) ||'-'|| SUBSTR(CHAR(zxdsdt),5,2) ||'-'|| SUBSTR(CHAR(zxdsdt),7,2))) from MVXAMODSFC.MZIMHS ORDER BY ZXDSDT DESC ')

Thx for all your help...Substr is very useful