convert cyymmdd (AS400) format to datetime format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

convert cyymmdd (AS400) format to datetime format

Author

Topic

house
Starting Member

3 Posts

Posted - 2004-05-26 : 19:23:10

trying to convert AS/400 date format in cyymmdd format (examples 1040526, 0990415) to sql datetime format so I can use all features of SQL date formating, datediff etc.. any help would be appreciated..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-26 : 19:31:26

[code]

SET NOCOUNT ON

DECLARE @date CHAR(7)

SET @date = '0990415'

SELECT ConvertedDate =
CASE
WHEN LEFT(@date, 1) = 1 THEN SUBSTRING(@date, 4, 2) + '-' + SUBSTRING(@date, 6, 2) + '-' + '20' + SUBSTRING(@date, 2, 2)
WHEN LEFT(@date, 1) = 0 THEN SUBSTRING(@date, 4, 2) + '-' + SUBSTRING(@date, 6, 2) + '-' + '19' + SUBSTRING(@date, 2, 2)
END

SET @date = '1040526'

SELECT ConvertedDate =
CASE
WHEN LEFT(@date, 1) = 1 THEN SUBSTRING(@date, 4, 2) + '-' + SUBSTRING(@date, 6, 2) + '-' + '20' + SUBSTRING(@date, 2, 2)
WHEN LEFT(@date, 1) = 0 THEN SUBSTRING(@date, 4, 2) + '-' + SUBSTRING(@date, 6, 2) + '-' + '19' + SUBSTRING(@date, 2, 2)
END

Results:

ConvertedDate
-------------
04-15-1999

ConvertedDate
-------------
05-26-2004
[/code]

Tara

house
Starting Member

3 Posts

Posted - 2004-05-26 : 19:42:46

thanx tara.. would anthing change if the as/400 date (cyymmdd) is in numeric form?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-26 : 19:48:40

Yes that will be a problem as you'll lose the 0 when c = 0. Here is a solution for that:



SET NOCOUNT ON

DECLARE @AS400Date INT
DECLARE @date CHAR(7)

SET @AS400Date = 0990415
SET @date = CASE WHEN LEN(@AS400Date) = 6 THEN '0' + CONVERT(CHAR(6), @AS400Date) ELSE CONVERT(CHAR(7), @AS400Date) END

SELECT ConvertedDate = 
	CASE 
		WHEN LEFT(@date, 1) = 1 THEN SUBSTRING(@date, 4, 2) + '-' + SUBSTRING(@date, 6, 2) + '-' + '20' + SUBSTRING(@date, 2, 2)
		WHEN LEFT(@date, 1) = 0 THEN SUBSTRING(@date, 4, 2) + '-' + SUBSTRING(@date, 6, 2) + '-' + '19' + SUBSTRING(@date, 2, 2)
	END

SET @AS400Date = 1040526
SET @date = CASE WHEN LEN(@AS400Date) = 6 THEN '0' + CONVERT(CHAR(6), @AS400Date) ELSE CONVERT(CHAR(7), @AS400Date) END

SELECT ConvertedDate = 
	CASE 
		WHEN LEFT(@date, 1) = 1 THEN SUBSTRING(@date, 4, 2) + '-' + SUBSTRING(@date, 6, 2) + '-' + '20' + SUBSTRING(@date, 2, 2)
		WHEN LEFT(@date, 1) = 0 THEN SUBSTRING(@date, 4, 2) + '-' + SUBSTRING(@date, 6, 2) + '-' + '19' + SUBSTRING(@date, 2, 2)
	END

Tara

house
Starting Member

3 Posts

Posted - 2004-05-26 : 19:53:05

Thanx Tara for that fast reply!! new to this stuff.. luv this forum..

thanx again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources