CODEWITHSUNDEEP
sql-servert-sql
NorseCode
NorseCode

Reputation: 121

MS SQL: checking if a timestamp is after expire month(int value)

I need to find expired credit cards in a table. The fields expire_year and expire_month are integer values.

I was thinking something like this could work:

select * 
from CREDITCARD 
where CURRENT_TIMESTAMP > DATEFROMPARTS(EXPIRE_YEAR, EXPIRE_MONTH, 1);

The problem with this is that the definition of expired would be the first day of the next month. Therefore I need to find a way to write EXPIRE_MONTH + 1. But this is also no good, as the month might be December, in which case I'd be looking for month number 13. In such cases, I'd need to bump the EXPIRE_YEAR instead, and set EXPIRE_MONTH to 1.

I´ve tried to google to the solution, but my issue seems a bit too specific. In Java this would be easy enough to solve, but my SQL knowledge is limited to fairly simple queries.

Upvotes: 0

Views: 129

Answers (1)

Killer Queen
Killer Queen

Reputation: 756

Something like that : 

SELECT DATEADD(month, 1, DATEFROMPARTS(EXPIRE_YEAR, EXPIRE_MONTH, 1))FROM MY_TABLE

Upvotes: 1

Related Questions