Creating a decade for a Datestamp which only returns the last digit of the year

So I'm trying to create a column for expiration date on a kind of card. The expiration date is encoded in the barcode as a 5 number string in the format ddmmy. The problem is that the card only returns the last digit of the year and I need the whole date. For example, if the part of the barcode which corresponds to the date is 19052 then the expiration date could be 19 May 1992, 19 May 2002, or 19 May 2012.

My work around was to pull the known year of the date a test was run on the card. What I’m trying to do is create an If statement that says IF the last digit of the expiration date = the last digit of the batch.StartTime year THEN expiration year = batch.StartTime year. ELSE IF last digit of expiration year = 0 and last digit of batch.StartTime year = 9 THEN expiration year = first 3 digits of the batch.StartTime year + last digit of expiration date + 10 (for the next decade). ELSE expiration year = first 3 digits of the batch.StartTime year + last digit of expiration.

The problem is my IF statement won’t work. I keep getting an incorrect syntax error by the IF. Also if I try to see the results just for each condition separately it tells me that it can’t convert the day and month (with ‘/’ between) to an INT even though I’m declaring them as characters. Here’s the code:

SELECT

(CAST(SUBSTRING(tbl_CardIdentification.CardId, 11,2) + '/' + --this is the day
SUBSTRING(tbl_CardIdentification.CardId, 13,2) + '/' AS CHAR(6)) + -- this is the month

(IF 
(SUBSTRING(tbl_CardIdentification.CardId, 15,1) =
(SUBSTRING(CAST(DATEPART(YY, tbl__Batch.Start) AS CHAR(4)), 4,1)))
SELECT SUBSTRING(CAST(DA开发者_StackOverflow社区TEPART(YY, tbl_Batch.Start) AS CHAR(4)),1,4)

ELSE IF  
(CAST(SUBSTRING(tbl_CardIdentification.CardId, 15,1) AS INT)) = 0 AND
(CAST(SUBSTRING(CAST(DATEPART(YY, tbl_Batch.Start) AS CHAR(4)), 4,1) AS INT)) = 9
SELECT (CAST((SUBSTRING(CAST(DATEPART(YY, tbl__Batch.Start) AS CHAR(4)), 1,3) +
SUBSTRING(tbl_CardIdentification.CardId, 15,1)) AS INT) + 10)

ELSE SELECT
(SUBSTRING(CAST(DATEPART(YY, tbl__Batch.Start) AS CHAR(4)), 1,3) +
SUBSTRING(tbl_CardIdentification.CardId, 15,1))

AS Card_Expiration

FROM

tbl_LoadProcess

JOIN tbl_Batch ON
tbl_Batch.LoadProcessSid = tbl_LoadProcess.LoadProcessSid

JOIN tbl_CardIdentification ON
tbl_CardIdentification.LoadProcessSid = tbl_LoadProcess.LoadProcessSid

IF is not allowed in the SELECT statement. You can replace it with CASE in your statement. It would look like this

SELECT

(CAST(SUBSTRING(tbl_CardIdentification.CardId, 11,2) + '/' + --this is the day
SUBSTRING(tbl_CardIdentification.CardId, 13,2) + '/' AS CHAR(6)) + -- this is the month

CASE
WHEN
  (SUBSTRING(tbl_CardIdentification.CardId, 15,1) =
  (SUBSTRING(CAST(DATEPART(YY, tbl__Batch.Start) AS CHAR(4)), 4,1)))
THEN
  SUBSTRING(CAST(DATEPART(YY, tbl_Batch.Start) AS CHAR(4)),1,4)

WHEN
  (CAST(SUBSTRING(tbl_CardIdentification.CardId, 15,1) AS INT)) = 0 AND
  (CAST(SUBSTRING(CAST(DATEPART(YY, tbl_Batch.Start) AS CHAR(4)), 4,1) AS INT)) = 9
THEN
  (CAST((SUBSTRING(CAST(DATEPART(YY, tbl__Batch.Start) AS CHAR(4)), 1,3) +
  SUBSTRING(tbl_CardIdentification.CardId, 15,1)) AS INT) + 10)

ELSE
  (SUBSTRING(CAST(DATEPART(YY, tbl__Batch.Start) AS CHAR(4)), 1,3) +
  SUBSTRING(tbl_CardIdentification.CardId, 15,1))
END

AS Card_Expiration

FROM

tbl_LoadProcess

JOIN tbl_Batch ON
tbl_Batch.LoadProcessSid = tbl_LoadProcess.LoadProcessSid

JOIN tbl_CardIdentification ON
tbl_CardIdentification.LoadProcessSid = tbl_LoadProcess.LoadProcessSid

The CASE operator includes other keywords, including WHEN, THEN, ELSE and END. Also, note that the SELECT keywords in your IF are also not needed.

I haven't actually tested this, so there may be issues with parenthesis or other minor syntax problems, but you essentially need to convert your IF statement to a CASE WHEN. To resolve your conversion to INT problem, wrap the entire statement in a CAST, e.g.:

CAST(CASE
  WHEN 
    SUBSTRING(tbl_CardIdentification.CardId, 15,1) = 
    RIGHT(CAST(DATEPART(YY, tbl__Batch.Start) AS CHAR(4)), 1)
  THEN
    CAST(DATEPART(YY, tbl_Batch.Start) AS CHAR(4))

  WHEN
    CAST(SUBSTRING(tbl_CardIdentification.CardId, 15,1) AS INT) = 0
    AND RIGHT(CAST(DATEPART(YY, tbl__Batch.Start) AS CHAR(4)), 1) = '9'

  THEN
    CAST(DATEADD(yy, 11, tbl__Batch.Start) AS CHAR(4))

  ELSE
    SUBSTRING(CAST(DATEPART(YY, tbl__Batch.Start) AS CHAR(4)), 1,3) +
    SUBSTRING(tbl_CardIdentification.CardId, 15,1)

  END AS CHAR(4)) AS Card_Expiration

I made a number of other simplifying modifications, the goal of which was to eliminate some unnecessary clutter/complexity from the code, thereby making it easier to see what it's doing and debug issues. Specifically:

• SUBSTRINGs that pull back the rightmost character converted to RIGHT
• SUBSTRINGs that pull back the entire string removed
• Converted a CAST to INT to a string comparison
• In the last case, rather than adding 10 to the card date, added 11 to the batch date. (Much simpler, and the same result, right?)

Good luck!

-Michael

This should work for you, a much simpler solution, just replace the appropriate names. I am returning the month, day and year and the full date (which is probably what you are interested in):

DECLARE @StartDate DATETIME
DECLARE @InputDate NCHAR(5)

SELECT @StartDate = '02/01/2000'
SELECT @InputDate = '19052'

SELECT LEFT(@InputDate, 2) AS MyDay, SUBSTRING(@InputDate, 3,2) AS MyMonth,
    LEFT(DATEPART(YEAR, @StartDATE),LEN(DATEPART(YEAR, @StartDate))-1) + RIGHT(@InputDate,1) AS MyYear, 
    CAST(SUBSTRING(@InputDate,3,2) + '/'+LEFT(@InputDate, 2) + '/'+
        LEFT(DATEPART(YEAR, @StartDATE),LEN(DATEPART(YEAR, @StartDate))-1) + RIGHT(@InputDate,1) AS DATETIME) AS FullDate