AS400 date conversion - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

AS400 date conversion

Author

Topic

Thijsver
Starting Member

2 Posts

Posted - 2011-09-13 : 04:26:11

I encountered a very strange date format in AS400 and would like to convert it to a workable sql date format. Any help is much appreciated..

examples of the date format:
F2F861F0F861F0F3
4040614040614040
F1F461F0F261F0F7

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 04:46:46

so what datevalue does F2F861F0F861F0F3 denote?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thijsver
Starting Member

2 Posts

Posted - 2011-09-13 : 04:48:31

I have no idea, looks like hex but that doesn't convert into anything useful

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-09-13 : 06:22:52

Perhaps it's some character encoding, where 61=/ or 61=.? 40 probably = space or blank. Just a wild guess!

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-09-13 : 08:33:20

If this works, then it's a lucky guess! Thinking that what you posted is some kind of character encoding I broke up each hex digit and assumed it represented some character. So for the hex F2F861F0F861F0F3 there are the characters F2, F8, 61, F0, F8, 61, F0, F3 . I assumed 61 is the delimiter between different parts of the date and then offset the smallest hex digit to 0 and it seems to come out with valid dates! I'm assuming 40 is just blank or something. Please let me know if this works with the other dates, I'm interested to see if my code breaking skills are any good!




DECLARE @Dates TABLE(HexDates char(16) Primary Key)

INSERT INTO @Dates
SELECT 'F2F861F0F861F0F3'
UNION ALL
SELECT '4040614040614040'
UNION ALL
SELECT 'F1F461F0F261F0F7'

--offset charater encoding is off from ascii/unicode
declare @Offset int=192


SELECT 

	Char(convert(varbinary,'0x' + Substring(HexDates,1,2),1)-@Offset)+
	Char(convert(varbinary,'0x' + Substring(HexDates,3,2),1)-@Offset)+
	+
	'/'
	+
	Char(convert(varbinary,'0x' + Substring(HexDates,7,2),1)-@Offset)+
	Char(convert(varbinary,'0x' + Substring(HexDates,9,2),1)-@Offset)+
	+
	'/'
	+
	Char(convert(varbinary,'0x' + Substring(HexDates,13,2),1)-@Offset)+
	Char(convert(varbinary,'0x' + Substring(HexDates,15,2),1)-@Offset)
FROM
	@Dates

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 08:37:04

gives me output as


output
----------------
NULL
14/02/07
28/08/03

But looks a really good guess though

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-09-13 : 08:43:05

Here's the character encoding!! I was right!

[url]http://en.wikipedia.org/wiki/Extended_Binary_Coded_Decimal_Interchange_Code[/url]

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-09-13 : 08:44:04

That's a date! That's what you want. 40 is just a space

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-09-13 : 08:44:27

So in other words 4040614040614040 is just an empty date

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-09-13 : 08:45:17

See the solved ones side by side with the hex:





DECLARE @Dates TABLE(HexDates char(16) Primary Key)

INSERT INTO @Dates
SELECT 'F2F861F0F861F0F3'
UNION ALL
SELECT '4040614040614040'
UNION ALL
SELECT 'F1F461F0F261F0F7'

--offset charater encoding is off from ascii/unicode
declare @Offset int=192


SELECT 

	Char(convert(varbinary,'0x' + Substring(HexDates,1,2),1)-@Offset)+
	Char(convert(varbinary,'0x' + Substring(HexDates,3,2),1)-@Offset)+
	+
	'/'
	+
	Char(convert(varbinary,'0x' + Substring(HexDates,7,2),1)-@Offset)+
	Char(convert(varbinary,'0x' + Substring(HexDates,9,2),1)-@Offset)+
	+
	'/'
	+
	Char(convert(varbinary,'0x' + Substring(HexDates,13,2),1)-@Offset)+
	Char(convert(varbinary,'0x' + Substring(HexDates,15,2),1)-@Offset)
	,
	HexDates
FROM
	@Dates

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-09-13 : 08:46:58

So 61 is actually a "/" according to the table on wiki! I'm quite pleased to have solved this before finding the wiki page :)

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-09-16 : 10:02:50

So did it work then?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-16 : 10:41:06

Don't ya hate when you work out a tough one with minimal information, and the OP never comes back to see the answer?

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-09-16 : 10:57:49

Yeah! Especially when I was really pleased with the result. Felt a little like that guy off the film "beautiful mind" seeing the pattern in the code! Except a little less crazy...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources