Against Date Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Against Date Value

Author

Topic

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-28 : 08:20:50

Hi,

I want to get all expired users and also allow 30days from the expiration date of the user.

My date value field is a text type.
Please suggest

When I am try this:
slect * from ALM,PV
WHERE ALM.USR_STATUS ='Disabled'
and PV.Active_Term = 'T'
and CONVERT(varchar(10), AD.DATE_Exp, 120) > DATEADD(d,DATEDIFF(d,0,GETDATE()),-30)
I get error: Incorrect syntax near '>'

when I try this: Returns 2 records.
slect * from ALM,PV
WHERE ALM.USR_STATUS ='Disabled'
and PV.Active_Term = 'T'
(DATEADD(d,DATEDIFF(day,0,AD.Date_Exp) > (DATEADD(d,DATEDIFF(day,0,AD.Date_Exp),+ 30)

Thanks,
Maachie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 08:21:47

you want 30 days forward or backward?

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-28 : 08:24:39

slect * from ALM,PV
WHERE ALM.USR_STATUS ='Disabled'
and PV.Active_Term = 'T'
and CONVERT(varchar(10), AD.DATE_Exp, 120) > DATEADD(d,DATEDIFF(d,0,GETDATE()),-30)

give the alias name properly.

AD.DATE_Exp

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-28 : 09:37:13

I want 30 days after every users expiration date.
for eg if my last date was june 27th, I want to see my name in the result set.
june-27th + 30days. So every users end dates are different.

pls see the alias name is correct.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 10:00:42

quote:
Originally posted by Maachie

I want 30 days after every users expiration date.
for eg if my last date was june 27th, I want to see my name in the result set.
june-27th + 30days. So every users end dates are different.

pls see the alias name is correct.

so whenever you look for results you need to see those who have expired that day upto those which are going to be expired in a month from today forward?

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-28 : 10:04:44

every users end date is going to be different right?
effective that end date add another 30days to the end date.

Thanks,
Maachie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 10:11:30

quote:
Originally posted by Maachie

every users end date is going to be different right?
effective that end date add another 30days to the end date.

Thanks,
Maachie

do you mean this then?

select *,
DATEADD(d,DATEDIFF(d,0,AD.DATE_Exp),+30) AS ExpirationDate
from ALM,PV
WHERE ALM.USR_STATUS ='Disabled'
and PV.Active_Term = 'T'

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-28 : 10:19:50

every users end date is going to be different right?
effective that end date add another 30days to the end date.

the result set should be every user has expired not only the actual end date but also the 30days from the end date.

Basically i want all users who are expired per their end date and expired additional 30 days.

Hope this helps.

Thank you very much,
Maachie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 10:24:16

quote:
Originally posted by Maachie

every users end date is going to be different right?
effective that end date add another 30days to the end date.

the result set should be every user has expired not only the actual end date but also the 30days from the end date.

Basically i want all users who are expired per their end date and expired additional 30 days.

Hope this helps.

Thank you very much,
Maachie

sounds like this

select *,
AD.DATE_Exp AS ExpirationDate
from ALM,PV
WHERE ((ALM.USR_STATUS ='Disabled'
and PV.Active_Term = 'T')
OR AD.DATE_Exp<DATEADD(d,DATEDIFF(d,0,GETDATE()),30))

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-28 : 10:44:35

these brings me both inactive and active records.
we can't use or

I want all disabled and expired users plus 30days added expired.

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-28 : 10:54:28

All set. This pc of code helped me from Vishak

select *,
DATEADD(d,DATEDIFF(d,0,AD.DATE_Exp),+30) AS ExpirationDate
from ALM,PV
WHERE ALM.USR_STATUS ='Disabled'
and PV.Active_Term = 'T'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources