Converting AS400 Century Date 1

[dvannoy]

I am using a linked server to an AS400..when getting data from the linked server and inserting it into another table, I need to have a date field converted to standard date...

01/01/2001

it now returns a century date...
how can i do this??

Thanks in advance dvannoy@onyxes.com

 

[tlbroadbent]

What do you mean by "Century date" and "Standard date?" What's the actual format in the linked table?

Remember that dates are not stored in a format in a SQL table. They are stored as numbers. You can choose styles or formats for displaying dates. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dvannoy]

OK, the AS400 will return a date of 1020201..this is

01/02/02

I will like to have the date of 01/02/02 show in my field..

Thanks dvannoy@onyxes.com

 

[tlbroadbent]

I'm not clear from the example which part of the AS400 date is the year, month and day. It seems to me that you'll need to use substring functions and concatenation to reformat to a SQL Server date.

SQLDate = Substring(AS400Date, 2, 2) + '/' +
Substring(AS400Date, 4, 2) + '/' +
Substring(AS400Date, 6, 2)

You may have to rearrange the statement to match the formats. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dvannoy]

Thanks for the info...where would be the best place to execute the code...in the front end app.. I created a field called SQLDate and tryed the code using an insert statement got some errors...saying unclosed brackets..tryed addind and removing brackets no luck...

Thanks dvannoy@onyxes.com

 

[tlbroadbent]

The answer depends on how you are doing the insert. With a linked table, I assumed you are using a T-SQL Insert statement. The code I provided could be used in T-SQL. If you are manipulating data in a front end app, you'll need to reformat the date accordingly. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dvannoy]

I keep getting an error..."Incorrect syntax near '='


SELECT *
FROM [dbo].[AS4001] SQLDate = Substring(DCBXDT, 2, 2) + '/' + Substring(DCBXDT, 4, 2) + '/' + Substring(DCBXDT, 6, 2)

maybe I am missing something or I donot fully understand

I am somewhat new to SQL...sorry dvannoy@onyxes.com

 

[tlbroadbent]

You are converting a column so the conversion must be placed in the query SELECT list.

SELECT
Col1, Col2, ...,
SQLDate = Substring(DCBXDT, 2, 2) + '/' +
Substring(DCBXDT, 4, 2) + '/' +
Substring(DCBXDT, 6, 2)
FROM [dbo].[AS4001] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dvannoy]

Thank you !! worked perfect dvannoy@onyxes.com

 

[Guest_imported]

You can convert an AS400 date in the format CYYMMDD to a conventional date by treating it as a long integer, adding 19000000 to it, then casting it to a datetime (SQL recognises YYYYMMDD)

 

[tlbroadbent]

rcrafts,

Thanks for the tip. I'd mark your post as helpful if you were a member. I recommend joining Tek-Tips. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[dvannoy]

tlbroadbent thanks for everything it works perfect..I am just trying to fit it into this code and cannot seem to make it work...I've tryed it in several different spots and no luck...do you know where I can place the substring code??
I even tryed adding the Library name in front of the field name and no luck...


Select * from openquery(AS400,'SELECT AEDCREP.DCKWCD, AEDCREP.DCHSTX, AEDCREP.DCLYCD, AEDCREP.DCLXCD, AEDCREP.DCCRTD, AEDCREP.DCBXDT, AEDDREP.DDLOCD,
AEDDREP.DDAIQT, AEDDREP.DDI2ST, AEDDREP.DDHTNB, AEDDREP.DDH9ST, AEDDREP.DDH6NB, AEDDREP.DDK2CD, AEDDREP.DDHUNB,
AEDDREP.DDHNTX, AEDDREP.DDHSNB, AEDDREP.DDKZCD, AEDDREP.DDK1CD, AEDDREP.DDIUST, AEDDREP.DDCZNA, AEGDREP.GDAZCD,
AEGDREP.GDCMCE
FROM AEDCREP INNER JOIN
AEDDREP ON AEDCREP.DCKWCD = AEDDREP.DDLOCD INNER JOIN
AEGDREP ON AEDCREP.DCLYCD = AEGDREP.GDCMCE
SUBSTRING(DCBXDT, 4, 2) + '/' + SUBSTRING(DCBXDT, 6, 2) + '/' + SUBSTRING(DCBXDT, 2, 2) AS [Date Received]
WHERE AEDCREP.DCBXDT >1020201 AND AEDCREP.DCLXCD = ''ZOAGC09880''')

Thanks again! dvannoy@onyxes.com

 

[tlbroadbent]

If you want the date to appear in the result, the code has to be in the select list before the FROM.

Select *,
SUBSTRING(DCBXDT, 4, 2) + '/' +
SUBSTRING(DCBXDT, 6, 2) + '/' +
SUBSTRING(DCBXDT, 2, 2) AS [Date Received]

FROM openquery(AS400, 'SELECT AEDCREP.DCKWCD,
AEDCREP.DCHSTX, AEDCREP.DCLYCD, AEDCREP.DCLXCD,
AEDCREP.DCCRTD, AEDCREP.DCBXDT, AEDDREP.DDLOCD,
AEDDREP.DDAIQT, AEDDREP.DDI2ST, AEDDREP.DDHTNB,
AEDDREP.DDH9ST, AEDDREP.DDH6NB, AEDDREP.DDK2CD,
AEDDREP.DDHUNB, AEDDREP.DDHNTX, AEDDREP.DDHSNB,
AEDDREP.DDKZCD, AEDDREP.DDK1CD, AEDDREP.DDIUST,
AEDDREP.DDCZNA, AEGDREP.GDAZCD,
AEGDREP.GDCMCE

FROM AEDCREP
INNER JOIN AEDDREP
ON AEDCREP.DCKWCD = AEDDREP.DDLOCD
INNER JOIN AEGDREP
ON AEDCREP.DCLYCD = AEGDREP.GDCMCE

WHERE AEDCREP.DCBXDT >1020201
AND AEDCREP.DCLXCD = ''ZOAGC09880''')
Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com