Convert various dates to 2 INT columns

Question

Post reply

Convert various dates to 2 INT columns

Express12

SSCoach

Points: 18514
More actions
June 30, 2005 at 12:14 pm

#88000

I need to convert a single column w/ several thousand credit card expiration dates and a handful of different formats into 2 target INT columns.

Source column: CardExpDate NVARCHAR(10) NULL

Target columns: CCExpMonth INT NOT NULL,
                CCExpYear   INT NOT NULL

Here are the various DATA SOURCE data content formats – all stored in the NVARCHAR(10) source column:

NULL
{spaces}
02/2005      (MM/YYYY)
0400         (MMYY)
072004      (MMYYYY)
02/00        (MM/YY)
04//99      (MM//YY)
02*99        (MM*YY)
3/3          (M/Y)
305          (MYY)
4-Dec       (Y-Mon)

Has anyone written a script to accommodate this type of conversion? Thx in advance.
BT

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

are those all possible formats or could there be more?

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

I assume that there are more that he didn't pick up yet.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

More possible formats

DECLARE @Month TABLE (MonthVal VARCHAR(10))

INSERT @Month VALUES ('MM')

INSERT @Month VALUES ('M')

INSERT @Month VALUES ('Mon')

INSERT @Month VALUES ('Month')

DECLARE @Year TABLE (YearVal VARCHAR(10))

INSERT @Year VALUES ('YYYY')

INSERT @Year VALUES ('YY')

INSERT @Year VALUES ('Y')

DECLARE @Seperator TABLE (Seperator VARCHAR(10))

INSERT @Seperator VALUES ('')

INSERT @Seperator VALUES (' ')

INSERT @Seperator VALUES (':')

INSERT @Seperator VALUES ('-')

INSERT @Seperator VALUES ('/')

INSERT @Seperator VALUES ('\')

SELECT MonthVal + Seperator + YearVal Formats FROM @Month, @Year, @Seperator

UNION ALL

SELECT YearVal + Seperator + MonthVal FROM @Month, @Year, @Seperator

Regards,
gova

Express12 SSCoach Points: 18514 More actions · Answer 4

Yes, this is then entire list of unique formats. This is a legacy system w/ no new info being added. THX

BT

Express12 SSCoach Points: 18514 More actions · Answer 5

Now I need data, in the varied formats - converted to the static target columns:

CCExpMonth INT NOT NULL,

CCExpYear INT NOT NULL

Examples:

02/2005 needs 02 stored in CCExpMonth and 2005 stored in CCExpYear

0400 needs 04 stored in CCExpMonth and 2000 stored in CCExpYear

etc, etc.

BT

David Burrows SSC Guru Points: 65142 More actions · Answer 6

Ok I'll try (and wait for the fallout )

SELECT CAST(SUBSTRING(CardExpDate,1,2) as int) AS [CCExpMonth],

CAST(CASE WHEN SUBSTRING(CardExpDate,5,2) > '50'

THEN STUFF(SUBSTRING(CardExpDate,3,4),1,2,'19')

ELSE SUBSTRING(CardExpDate,3,4)

END as int) AS [CCExpYear]

FROM (

SELECT

CASE

WHEN CardExpDate IS NULL THEN '000000'

WHEN RTRIM(CardExpDate) = '' THEN '000000'

WHEN PATINDEX('[0-9][0-9]/[0-9][0-9][0-9][0-9]',CardExpDate) > 0 THEN STUFF(CardExpDate,3,1,'')

WHEN PATINDEX('[0-9][0-9][0-9][0-9]',CardExpDate) > 0 THEN STUFF(CardExpDate,3,0,'20')

WHEN PATINDEX('[0-9][0-9][0-9][0-9][0-9][0-9]',CardExpDate) > 0 THEN CardExpDate

WHEN PATINDEX('[0-9][0-9]/[0-9][0-9]',CardExpDate) > 0 THEN STUFF(CardExpDate,3,1,'20')

WHEN PATINDEX('[0-9][0-9]//[0-9][0-9]',CardExpDate) > 0 THEN STUFF(CardExpDate,3,2,'20')

WHEN PATINDEX('[0-9][0-9]*[0-9][0-9]',CardExpDate) > 0 THEN STUFF(CardExpDate,3,1,'20')

WHEN PATINDEX('[0-9]/[0-9]',CardExpDate) > 0 THEN '0'+STUFF(CardExpDate,2,1,'200')

WHEN PATINDEX('[0-9][0-9][0-9]',CardExpDate) > 0 THEN '0'+STUFF(CardExpDate,2,0,'20')

WHEN PATINDEX('[0-9]-[A-Z]%',CardExpDate) > 0 THEN REPLACE(STR(MONTH('01 jan 1900'),2,0),' ','0')+'200'+SUBSTRING(CardExpDate,1,1)

ELSE CardExpDate

END AS [CardExpDate]

FROM

) x

Far away is close at hand in the images of elsewhere.
Anon.

Express12 SSCoach Points: 18514 More actions · Answer 7

Thanks! This appears to be working! MUCH Appreciated David.

BT

Express12 SSCoach Points: 18514 More actions · Answer 8

Hi - your attached SQL works appropriately for everything except those nvarchar(10) values in the format MM/YY. Example: 10/02 is not being formatted !

thx

BT

David Burrows SSC Guru Points: 65142 More actions · Answer 9

Works OK for me, what result do you get (or do you get an error?)

Far away is close at hand in the images of elsewhere.
Anon.

Express12 SSCoach Points: 18514 More actions · Answer 10

Just found my problem. It works ok for some of the MM/YY values and doesn't work for others. (probably the internal hex representation varies though the display looks like MM/YY)

I applied this fix -- using RTRIM and it worked !!!

when PATINDEX('[0-9][0-9]/[0-9][0-9]',rtrim(CardExpDate)) > 0 then stuff(CardExpDate,3,1,'20')

thx David-

BT

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

The column should be rtrimed automatically if you have a [n]varchar column. Are you sure that there are trailing spaces at the end . Or is it just another version of the space character?

Express12 SSCoach Points: 18514 More actions · Answer 12

I encountered another new format: MMYY. (w/ a period as the 5th byte) The actual data value in the column is "1101." Can you help me format this one? I've tried:

when PATINDEX('[0-9][0-9][0-9][0-9].',rtrim(CardExpDate)) > 0 then stuff(CardExpDate,5,1,'20')

and it's returning: 110120

I'll need 112001 returned from your inner PATINDEX.

thx

BT

David Burrows SSC Guru Points: 65142 More actions · Answer 13

The column should be rtrimed automatically if you have a [n]varchar column

not quite, e.g.

DECLARE @value nvarchar(10)

SET @value = N'10/02 '

SELECT LEN(@value),DATALENGTH(@value),PATINDEX('[0-9][0-9]/[0-9][0-9]',@value)

will result in

5 12 0

Far away is close at hand in the images of elsewhere.
Anon.

David Burrows SSC Guru Points: 65142 More actions · Answer 14

WHEN PATINDEX('[0-9][0-9][0-9][0-9][.]',CardExpDate) > 0 THEN STUFF(LEFT(CardExpDate,4),3,0,'20')

p.s. you can do the RTRIM as well if you wish

Far away is close at hand in the images of elsewhere.
Anon.