building a date

Question

Post reply

building a date

mike 57299

SSCrazy

Points: 2529
More actions
March 13, 2010 at 7:03 pm

#216664

Hi all,
I received great help before from this forum. Thanks! 🙂 I have now run into another problem. Here is the code:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#CC','U') IS NOT NULL
DROP TABLE #CC
--===== Create the test table with
CREATE TABLE #CC
(
cc_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
cc char(30),
exp_mo char(2),
exp_yr char(2)
)
INSERT INTO #CC (cc,exp_mo,exp_yr) values
('1234567890', '02', '11'),
('4845874585', '01', '12')
select * from #cc
select dateadd( D, 30, ''''+rtrim( exp_mo) + '/' +
case
when exp_mo IN ('1','3','5','7','8','10','12','01','03','05','07','08') THEN '31'
when exp_mo IN ('2','02') then '28'
else '30' end + '/20' + RTRIM( exp_yr) + '''')
from #cc
Here is the result messages:
(2 row(s) affected)
(2 row(s) affected)
Msg 241, Level 16, State 1, Line 20
Conversion failed when converting date and/or time from character string.
Why am I getting this error message?
Thanks for any help.
Mike

Viewing 3 posts - 1 through 2 (of 2 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I'd expect the problem is within the setting of your DATEFORMAT parameter.

But the major problem is: you're trying to treat a date value as a string.

If it's a date, use it as a date.

It seems like you want to get the last day for a given month.

The following code would do it as well.

Side notes: I hope the format itself is used for display purposes only. If not, please consider using the date as it is and remove the CONVERT part.

Secondly, if you store montha nd year values in a column I don't see a reason to do it as character value. Change it to INT and the code below will become even easier.

SELECT CONVERT(CHAR(10),DATEADD(mm,CAST(exp_mo AS INT),DATEADD(yy,CAST(exp_yr AS INT),'20000101'))-1,101)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Lutz,

It's unfortunate, but these are credit card expiration dates and they're (unfortunately) frequently provided as CHAR(2) for the month and year entries. Sometimes, they're provided as CHAR(5) mm/yy. It depends on the provider of the information but it's very rare that these bloody dates come through as an actual date.

You solved the OP's problem correctly. I typically get around this problem using a computed column in a table using a concatenation formula because DATEDIFF/DATEADD don't make for a deterministic column that I can put an index on. Admittedly, I've not tried using an Indexed View for such a thing (if it's possible in this case) but if it is, that may be an even better solution.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)