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'
精彩评论