Convert Credit Card expiry to date 1

[IanWaterman]

I have a field which holds credit card expiry

1113 which is MMYY

Is there an easy way to convert this to last day of that month.

I can only think to break up and create first of month by casting elements to number and then converting to date, then use dateadd to add a month and then subtract 1 to get back to last day of month.

Thanks

Ian

 

[djj55]

Breaking it up is how I would do it. (one of many ways)

Look at FAQ for finding the last day of a month.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

What is the data type for the column?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IanWaterman]

Its a Varchar(10)

This is what I have done, if there is a better/faster/cleaner way please let me know, also which of the two methods is the most efficient?

select Expirydate, 
cast('20'+substring(Expirydate,3,2)+'-'+substring(Expirydate,1,2)+'-01'as DATE) FirstMonth,
DATEADD(mm, DATEDIFF(mm, 0, cast('20'+substring(Expirydate,3,2)+'-'+substring(Expirydate,1,2)+'-01'as DATE))+1, -1) ExpDate,
DATEADD(d, -1, DATEADD(mm, 1, cast('20'+substring(Expirydate,3,2)+'-'+substring(Expirydate,1,2)+'-01'as DATE))) ExpDate2
from PolicyCommIdea

I only need the last ExpDate or ExpDate2 just broke it down so I could debug process

Interestingly when query displyed on screen in management Studio ExpDate comes back as a Datetime 2014-02-28 00:00:00.000
Whereas FirstMonth and ExpDate are just dates 2014-02-28

Why does that happen?

I also tried using

DATEADD(mm, 1, cast('20'+substring(Expirydate,3,2)+'-'+substring(Expirydate,1,2)+'-01'as DATE))-1

But that failed with error
Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int

which is odd as select getdate()-1 returns yesterdays date


Ian

 

[gmmastros]

This is what I have done, if there is a better/faster/cleaner way please let me know, also which of the two methods is the most efficient?

Neither one is going to be terribly efficient because you are basically forced to do string manipulations, data type conversions, and date arithmetic. Personally, I would use the last one, but that is merely a preference, my preference.

Interestingly when query displyed on screen in management Studio ExpDate comes back as a Datetime 2014-02-28 00:00:00.000
Whereas FirstMonth and ExpDate are just dates 2014-02-28

Why does that happen?

Let's look at the 2nd column...

DATEADD(mm, DATEDIFF(mm, 0, cast('20'+substring(Expirydate,3,2)+'-'+substring(Expirydate,1,2)+'-01'as DATE))+1, -1) ExpDate,

For clarity, let's replace the cast statement with the data type.


[tt]DATEADD(mm, DATEDIFF(mm, 0, [!]<DateDataType>[/!])+1, -1) ExpDate,[/tt]

Now, let's evaluate the expression in the same order that SQL Server evaluates it.

[tt]DATEADD(mm, [!]DATEDIFF(mm, 0, <DateDataType>)[/!]+1, -1) ExpDate,[/tt]

DateDiff always returns an integer, so we have:

[tt]DATEADD(mm, [!]<Integer>[/!]+1, -1) ExpDate,[/tt]

The 3rd argument for the DateAdd function should be a DateTime or Date. Since you are using -1 (which is an integer), SQL Server will automatically convert this to a DateTime data type based on data type precedence.

http://msdn.microsoft.com/en-us/library/ms190309.aspx

If the 3rd argument for DateAdd is a DateTime data type, DateAdd returns a DateTime data type, which explains why you are getting DateTime instead of Date.

The 3rd argument you are using for DateAdd is -1, this represents December 31, 1899, so you could do this instead:

[tt]
DATEADD(mm, DATEDIFF(mm, 0, cast('20'+substring(Expirydate,3,2)+'-'+substring(Expirydate,1,2)+'-01'as DATE))+1, [!]Cast('18991231' As Date)[/!]) ExpDate,
[/tt]

In the 3rd column from your example, you add 1 month to a DATE data type, and then subtract 1 day from a DATE data type, so everything remains as a DATE data type.

I also tried using

DATEADD(mm, 1, cast('20'+substring(Expirydate,3,2)+'-'+substring(Expirydate,1,2)+'-01'as DATE))-1

But that failed with error
Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int

which is odd as select getdate()-1 returns yesterdays date

GetDate() returns a DateTime data type, which allows you to use the + and - operators. The Date data type does not support it.

Ex:

Select Cast(GetDate() As Date)-1

This code above returns an error:
[tt][red]
Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int
[/red][/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IanWaterman]

George

Thank you for your comprehensive answer, very helpful.

Ian