Checking expiry dates in a table

Question

Post reply

Checking expiry dates in a table

smthembu

Hall of Fame

Points: 3464
More actions
July 15, 2010 at 4:30 am

#141039

Hi All
I need help, i a have table with our security certificates and there's a coloumn with expiry date. What i need is to check which certificate will be expiring in a months time then send a notification to security. the date format is 2010/02/04 yyyy/mm/dd
email body script i do have.
Tx
It's better to fail while trying, rather than fail without trying!!!

Viewing 8 posts - 1 through 7 (of 7 total)

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

magasvs SSCertifiable Points: 7660 More actions · Answer 1

Something like this:

SELECT * FROM dbo.YourTable

WHERE YourColumn =

CAST(DATEPART(year,DATEADD(month,1,GETDATE())) as VARCHAR(4))+'/'

+REPLICATE('0', 2 - DATALENGTH(CAST(DATEPART(month,DATEADD(month,1,GETDATE())) as VARCHAR(2))))

+CAST(DATEPART(month,DATEADD(month,1,GETDATE())) as VARCHAR(2))+'/'

+REPLICATE('0', 2 - DATALENGTH(CAST(DATEPART(day,DATEADD(month,1,GETDATE())) as VARCHAR(2))))

+CAST(DATEPART(day,DATEADD(month,1,GETDATE())) as VARCHAR(2))

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

I don't believe that it needs to be quite that complex. Unless I'm somehow missing the big picture, something like the following should do...

SELECT * FROM dbo.YourTable

WHERE YourColumn BETWEEN GETDATE() AND DATEADD(mm,1,GETDATE())

If you made this mistake of storing the date as a varchar in that format, you may have to use something like the following:

SELECT * FROM dbo.YourTable

WHERE REPLACE(YourColumn,'/','') BETWEEN GETDATE() AND DATEADD(mm,1,GETDATE())

--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)

magasvs SSCertifiable Points: 7660 More actions · Answer 3

There ar emany ways to do it. This is just an example for the date in character format

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 4

magasvs (7/16/2010)
There ar emany ways to do it. This is just an example for the date in character format

Understood. So why not pick a simple way?

--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)

magasvs SSCertifiable Points: 7660 More actions · Answer 5

That was Friday's madness 🙂

SELECT * FROM YourTable

WHERE convert (datetime,YourColumn) =

DATEADD(month,1,convert (datetime,convert(varchar(10), getdate(), 101), 101))

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

magasvs (7/17/2010)
That was Friday's madness 🙂
SELECT * FROM YourTable
WHERE convert (datetime,YourColumn) =
DATEADD(month,1,convert (datetime,convert(varchar(10), getdate(), 101), 101))

Heh... madness indeed.

--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)

smthembu Hall of Fame Points: 3464 More actions · Answer 7

Thanks!!

It's better to fail while trying, rather than fail without trying!!!