Create Query

[NEL3644]

I have 3 fields in one of the tables in my database that looks like:

Table name: Expiration

BeginDate EndDate E-mail
========= ======= =======
20001023 20001024 joe@yahoo.com
20001025 20001027 mike@yahoo.com
20001109 20001116 jmarcus@hotmail.com
.
.
.
........and so on...


Can anyone help me create a query where I can be able to always identify a date 1 week prior to last expired date (EndDate)?

This query will help me send an automatic e-mail that notifies a user that the pswd will expire in one week!

Thank you in advance!

 

[Durkin]

Use the DATEADD function. To get all users where whos passwords will expire a week from today use:

SELECT * FROM TABLE1 WHERE DATEADD(wk, -1, EndDate) = GETDATE() Durkin
alandurkin@bigpond.com

 

[NEL3644]

What does GETDATE function do?

 

[foxdev]

GetDate() is the system date (today).

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

And no, it doesn't actually get you a date for friday night! LOL

 

[foxdev]

But in SQL Server 2000 you could write a user-defined function to do that :-V

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

No you couldn't, actually, because user-definded functions in 2000 cannot be non-deterministic..


Tom

 

[foxdev]

Mmmm...I hate to disagree with the master, but the indications from my research are that SQLS2K UDFs can be non-deterministic. However, it is true that you cannot use a non-deterministic function (such as the builtin GetDate function) in a user defined function.

In any event, my GetDate UDF would always return -1. My server insists that it be so.

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

Yes, if you call a udf and always get the same value back based on the same inputs, then it will work..

Tom

 

[TomSark]

But, I tried it with Beta 2, and even placing non-deterministic calls in it prevented compilation....

Tom

 

[NEL3644]

I'm sorry about my limited knowledge about SQL, but if I wanna learn more I have to ask you guys what non-deterministic user defined functions are.....

 

[foxdev]

Tom, I don't have SQLS2K installed, so I'll have to rely on your reconnaissance.

NEL3644, a deterministic function is one that will always return the same value if passed the same argument(s). For example, ABS(-2) always returns the same value.

A non-deterministic function is one that, even with the same arguments, returns different values. The example we've been using is GETDATE(), which returns a different value every second.

Robert Bradley

http://www.foxdev.com/