Mapics 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 2000 Forums

Transact-SQL (2000)

Mapics Date Conversion

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2003-07-01 : 19:13:30

I’m having trouble trying to get ‘EDATM’ converted over to a date format. The field ‘EDATM’ is a seven-position CYYMMDD format (where C=Centruy). Ex:

Date | MMDDYY | YYMMDD | CYYMMDD
January 1, 1999 | 01 01 99 | 99 01 01 | 0 99 01 01
January 1, 2000 | 01 01 00 | 00 01 01 | 1 00 01 01

A century code of 0 specifies a year between 1900 and 1999. A century code of 1 specifies a year between 2000 and 2099. This was done to solve the Y2K problem for Mapics.

Table as it appears:

ITNBR | EDATM | DATE
050316 | 960725 |
068569 | 1010615 |
093442 | 960321 |

The result I would like to have is:

ITNBR | EDATM | DATE
050316 | 960725 | 07/25/96
068569 | 1010615 | 06/15/01
093442 | 960321 | 03/21/96

When I try the ‘substring’ function, it doesn’t come out correctly when there are only 6 digits. Its as if I need to find a way to put a ‘0’ in front of the six digit (EDATM) fields.

SELECT I.ITNBR, PSTC.EDATM AS EDATM, SUBSTRING(PSTC.EDATM,4,2) + '/'
+ SUBSTRING(PSTC.EDATM,6,2) + '/' + SUBSTRING(PSTC.EDATM,2,2) AS DATE
FROM dbo.BOM_ITEMASA I INNER JOIN
dbo.BOM_PSTRUC PSTC ON I.ITNBR = PSTC.CINBR
WHERE (PSTC.PINBR = '$PKC$')

[RESULT]

ITNBR | EDATM | DATE
050316 | 960725 | 72/5 /60
068569 | 1010615 | 06/15/01
093442 | 960321 | 32/1 /60

SQL Server 2000

ITNBR = numeric
EDATM = char(7)

Anyone have any ideas on what I should try to do first. Is there a way to get the field to pad it with a ‘0’ in front of any 6 digits?

Thanks, JLM

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-01 : 19:34:21

SELECT I.ITNBR, PSTC.EDATM AS EDATM,
Convert(datetime, CASE WHEN Len(EDATM)=7 AND Left(EDATM,1)='1' THEN '20' ELSE '19' END + Right(EDATM, 6)) AS DATE
FROM dbo.BOM_ITEMASA I INNER JOIN dbo.BOM_PSTRUC PSTC ON I.ITNBR = PSTC.CINBR
WHERE PSTC.PINBR = '$PKC$'

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2003-07-02 : 14:49:48

Thanks for the help but it doesn't seem to work correctly.
I've tried it with several fields that have just 6 digits in them and it works fine but when I tried it with fields that have 7 digits, it doesn't like it. I get this error: Syntax error converting datetime from character string.

I tried running this in SQL Analyzer and it only returned 2 rows before giving me that error msg.
Here is the results it returned:

ITNBR | EDATM | DATE
050316 | 1010628 | 2001-06-28 00:00:00.000
068569 | 1010628 | 2001-06-28 00:00:00.000

Here is an example of a table that I tried to have it convert over:

ITNBR | EDATM | DATE
050316 | 1010628 |
068569 | 1010628 |
099226 | 0 |
1202338 | 1021213 |
099227 | 0 |

JLM

Edited by - jose1lm on 07/02/2003 14:52:30

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-02 : 18:31:31

Well, you didn't SAY that you had data that was less than six characters

How are the zeros supposed to be converted?

Here's one method that should at least avoid the errors:

SELECT I.ITNBR, PSTC.EDATM AS EDATM,
Convert(datetime, CASE WHEN EDATM='0' THEN '01/01/190'
WHEN Len(EDATM)=7 AND Left(EDATM,1)='1' THEN '20' ELSE '19' END + Right(EDATM, 6)) AS DATE
FROM dbo.BOM_ITEMASA I INNER JOIN dbo.BOM_PSTRUC PSTC ON I.ITNBR = PSTC.CINBR
WHERE PSTC.PINBR = '$PKC$'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources