less than or equal to getdate? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

less than or equal to getdate?

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2008-06-02 : 09:42:28

SELECT website
FROM dbo.wce_contact
WHERE (NOT (Mail1Date IS NULL))
AND (Mail2Date IS NULL)
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail13Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
AND (IDStatus IS NULL OR IDStatus = '')
AND (NOT (Task LIKE '%x%') OR Task IS NULL)
AND (NOT (ExpressEmail IS NULL OR ExpressEmail = ''))
AND (NOT (WebSite IS NULL OR WebSite = ''))
AND (Mail17Date IS NULL OR Mail17Date = '')
AND (Mail18Date IS NULL)
AND (Mail1Date <= '20080421')
AND (NOT (RECORDOWNER = 'lbm') OR RECORDOWNER IS NULL)

How would i get the part in red to say mail1date less than or equal to 42 days back from todays date?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 09:47:43

Have a look at DATEADD() function and GETDATE() function in books online.

Topaz
Posting Yak Master

199 Posts

Posted - 2008-06-02 : 09:49:16

I hate using BOL, never once have i managed to understand it...

raky
Aged Yak Warrior

767 Posts

Posted - 2008-06-02 : 09:51:14

quote:
Originally posted by Topaz

SELECT website
FROM dbo.wce_contact
WHERE (NOT (Mail1Date IS NULL))
AND (Mail2Date IS NULL)
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail13Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
AND (IDStatus IS NULL OR IDStatus = '')
AND (NOT (Task LIKE '%x%') OR Task IS NULL)
AND (NOT (ExpressEmail IS NULL OR ExpressEmail = ''))
AND (NOT (WebSite IS NULL OR WebSite = ''))
AND (Mail17Date IS NULL OR Mail17Date = '')
AND (Mail18Date IS NULL)
AND (Mail1Date <= '20080421')
AND (NOT (RECORDOWNER = 'lbm') OR RECORDOWNER IS NULL)

How would i get the part in red to say mail1date less than or equal to 42 days back from todays date?

AND ( MailDate <= DATEADD(DD,-42,GETDATE()))

Topaz
Posting Yak Master

199 Posts

Posted - 2008-06-02 : 09:52:22

nice work thanks!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 09:56:12

quote:
Originally posted by Topaz

I hate using BOL, never once have i managed to understand it...

May be because you're always being spoon feeded with answers.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-02 : 10:18:37

You should consider reading up on Normalization and cleaning up your database design. You should never have columns in your table like Mail1Date, Mail2Date, etc. With a good design, your sql is much, much shorter and easier to write and much more efficient.

See:

http://www.datamodel.org/NormalizationRules.html

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-02 : 10:20:47

Incredible. Even the OP know the answer for himself.
In this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103778
the requirement is exactly the same!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources