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

Date Value

Author

Topic

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-25 : 14:12:58

Hi,

I want to get the value of AD.date_exp field.

I want to get users that are expired and also include 3days from the date expired.

For example: A usr was expired 2008-06-24, He should appear in my result set as of today 2008-07-25. Because his expiration date is 6/24 + 30days from that date.

Currently this is the pc of code i have that does not give me what i want.

CONVERT(varchar(10), AD.Date_Exp, 120) > dateadd(day, 30, GetDate())

thanks,
Maachie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 14:21:58

select values from table where date_exp >DATEADD(d,DATEDIFF(d,0,GETDATE(),-30)

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-25 : 14:29:13

I tried this

select CONVERT(varchar(10), AD.Date_Exp, 120) from corp_emp_ids AD
where Ad.date_exp >DATEADD(d,DATEDIFF(d,0,GETDATE(),-30)

The data type is actually text. I am using varchar to convert.

I get an error:
The datediff function requires 3 argument(s).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 14:33:10

quote:
Originally posted by Maachie

I tried this

select CONVERT(varchar(10), AD.Date_Exp, 120) from corp_emp_ids AD
where Ad.date_exp >DATEADD(d,DATEDIFF(d,0,GETDATE()),-30)

The data type is actually text. I am using varchar to convert.

I get an error:
The datediff function requires 3 argument(s).

missed a braces

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-25 : 14:45:36

This is the sample data in the field

AD.Date_Exp
8/24/2008 8:52
2/25/2008 10:00
10/22/2008 8:52
10/22/2008 8:52
1/18/2009 9:52
12/27/2008 10:00
1/18/2009 9:52
11/16/2008 10:00
12/6/2008 9:52

I am using this:
select AD.Date_Exp from corp_emp_ids AD
where Ad.date_exp >DATEADD(d,DATEDIFF(day,0,AD.Date_Exp),-30)

What i want is, that the data should include 30days from the date the user has expired.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 14:50:48

quote:
Originally posted by Maachie

This is the sample data in the field

AD.Date_Exp
8/24/2008 8:52
2/25/2008 10:00
10/22/2008 8:52
10/22/2008 8:52
1/18/2009 9:52
12/27/2008 10:00
1/18/2009 9:52
11/16/2008 10:00
12/6/2008 9:52

I am using this:
select AD.Date_Exp from corp_emp_ids AD
where Ad.date_exp >DATEADD(d,DATEDIFF(day,0,AD.Date_Exp),-30)

What i want is, that the data should include 30days from the date the user has expired.

why are you using AD.Date_Exp on right hand side? you need to check status as of today right replace AD.Date_Exp with GETDATE() on right part then.

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-25 : 14:54:57

this is what i get
The datediff function requires 3 argument(s).

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-25 : 14:58:13

let me once again explain what i am looking for
user has expired, I want to get the date he has expired and include 30days to the expired date.

expired date should include 30days to the expired date of any users
from the date the user has expired.

how would i do that?
Thanks,
Maachie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 15:02:25

quote:
Originally posted by Maachie

this is what i get
The datediff function requires 3 argument(s).

didnt you see my modified post?


select CONVERT(varchar(10), AD.Date_Exp, 120) from corp_emp_ids AD
where Ad.date_exp >DATEADD(d,DATEDIFF(d,0,GETDATE()),-30)

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-25 : 15:15:18

i tried that as well seems not right.

here this is what the data looks like
usr_start_date usr_end_date USR_END_DATE Date_Exp
9/4/2006 7/8/2008 07/08/08 7/8/2008 9:00
10/22/2007 7/4/2008 07/04/08 7/4/2008 8:52

7/8 is the expiration date of the user but has to include 30days. its only 17 days as of today.

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-07-25 : 15:35:30

this is what i am using

select usr_start_date, usr_end_date, USR_END_DATE, Date_Exp from mytable
where active_status = 0
and AD.DATE_Exp >DATEADD(d,DATEDIFF(d,0,GETDATE()),-30)

usr_start_date usr_end_date USR_END_DATE Date_Exp
9/4/2006 7/8/2008 07/08/08 7/8/2008 9:00
10/22/2007 7/4/2008 07/04/08 7/4/2008 8:52

7/8 is the expiration date of the user but has to include 30days. its only 17 days as of today 7/25.
Just FYI, the date_exp field is a text field with date data.

thanks
Maachie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources