Query to check Date in Sql

My query is simple yet bit twisted for me. Actually I am working on registration module of an app, where the registration expires 31st March every year. The registration is valid from April 1 to March 31. So whenever a user is registered in between the date, I want his status to be expired if march 31 is crossed.

Let me make more clear to you.

Say I have registered my self in 15Nov2010, then on 31st March 2011, my subscription will get expired. I want to check it automatically as the years will go on. I need a query that will automatically query the created date with开发者_运维问答 expiration date. I am already having a select query and i need to embed this condition and i want to check the creation date with current system date. If Current system date is not 31 march midnight 12, the status must be active else expired.

---

This can be used for MS SQL to determine whether it has expired or not.

create table #t
(
CreateDate datetime
)


Insert Into #t
select GETDATE() union all
Select DateAdd(month,4, getdate())

Select Case When CreateDate < getdate() And 
       Getdate() < Cast(str(DatePart(year, getdate())) + '-03-31' as datetime) Then 
       'Active' Else 'Expired' end as  [Status],
       CreateDate

From #t


drop table #t

To filter your query you would simply move the case statement to a where clause

e.g.

Where Case When CreateDate < getdate() And 
           Getdate() < Cast(str(DatePart(year, getdate())) + '-03-31' as datetime) Then 
           'Active' Else 'Expired' end  = 'Expired'

---

What you need is to schedule a job (i think they're called events on MySql) to run every year on March 31 11:59 and update set the status of all your accounts to expired. (remember to make dstinction on admin accounts) :)

Take a look at this.

for MySQL

http://dev.mysql.com/tech-resources/articles/mysql-events.html#1

for SqlServer

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

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

---

I am considering a table YourTable and it has a column Date of type datetime

You can use this query -

select [Date], dbo.GetStatus([Date]) as 'status' from YourTable

And, the function GetStatus -

CREATE FUNCTION [dbo].[GetStatus](@Date datetime)
RETURNS varchar(10) 
AS 
BEGIN
    DECLARE @Return varchar(10)
    DECLARE @Year int
    SELECT @Year = DATEPART(YYYY,GETDATE())    

    IF GETDATE() >= CONVERT(datetime,'01-APR-' + CONVERT(varchar,@Year))
        SET @Year = @Year + 1

    IF @Date BETWEEN CONVERT(datetime,'01-APR-' + CONVERT(varchar,@Year-1)) AND CONVERT(datetime,'31-MAR-' + CONVERT(varchar,@Year))
        set @return = 'active'
    ELSE
        set @return = 'inactive'

    Return @return

END;

---

I'd probably have a "last renewed" column (It would initially store the creation date) then write:

SELECT CASE WHEN 
    YEAR(DATEADD(mm, -3, LastRenewed)) < (YEAR(GETDATE()) - 1) 
    THEN 'Active'
    ELSE 'Expired' END 
    AS Status
    FROM TableName

I don't see what the problem with @Barry's answer is though, to be honest. If you need to use this logic in several places you can avoid repeating yourself using a view eg:

CREATE VIEW ActiveOrNot AS
    SELECT Account, CASE WHEN 
        YEAR(DATEADD(mm, -3, LastRenewed)) < (YEAR(GETDATE()) - 1) 
        THEN 'Active'
        ELSE 'Expired' END 
        AS Status
        FROM TableName

You could then select only active accounts using:

SELECT Account
FROM ActiveOrNot
WHERE Status = 'Active'