for dates in query need equivalent to sql server syntax

In the query below,
on the where statement. is there a method the same as  "datediff"
WHERE ((DateDiff('d',[date_proposal_completed],Date())<+30) AND ((dbo_temp_proposal_survey_results.wl_status) Is Null));
I need to change this syntax to sql server syntax. Please.


SELECT dbo_temp_proposal_survey_results.proposal_id, dbo_temp_proposal_survey_results.specialist_responsible, dbo_temp_proposal_survey_results.customer_name, dbo_temp_proposal_survey_results.date_proposal_completed, dbo_temp_proposal_survey_results.seller_racf_id, dbo_temp_proposal_survey_results.seller_email, dbo_temp_proposal_survey_results.wl_emailed_on, dbo_temp_proposal_survey_results.wl_survey_completed_on, dbo_temp_proposal_survey_results.wl_status, dbo_temp_proposal_survey_results.wl_Q1_answer, dbo_temp_proposal_survey_results.wl_Q2_answer, dbo_temp_proposal_survey_results.wl_Q3_answer, dbo_temp_proposal_survey_results.wl_Feedback, dbo_temp_proposal_survey_results.wl_ReasonLoss
FROM dbo_temp_proposal_survey_results
WHERE ((DateDiff('d',[date_proposal_completed],Date())<+30) AND ((dbo_temp_proposal_survey_results.wl_status) Is Null));

Open in new window



Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Total air code.  I do my own stunts too>
SELECT    
   proposal_id, specialist_responsible, customer_name, date_proposal_completed, seller_racf_id, 
   seller_email, wl_emailed_on, wl_survey_completed_on, wl_status, wl_Q1_answer, 
   wl_Q2_answer, wl_Q3_answer, wl_Feedback, wl_ReasonLoss
FROM dbo_temp_proposal_survey_results
WHERE DATEDIFF(day, date_proposal_completed, GETDATE()) <= 30 
   AND wl_status IS NULL

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>on the where statement. is there a method the same as  "datediff"
Yep.  That would be DATEDIFF.
0
TONY TAYLORCommented:
So here is the explanation:

While the function name is the same, there ARE differences between MS Access and SQL Server.  In MS Access, the DateDiff uses a string value ('d') to pass to the first parameter.  In SQL, the DateDiff function does NOT use a string, but a constant variable such as "day" (without quotes).  Additionally, while Access has Now() as the function that returns the current date and time, the GETDATE() function does that for SQL.

If you specifically do not want the timestamp on the value, I believe it would be best wrote as follows:

SELECT    
   proposal_id, specialist_responsible, customer_name, date_proposal_completed, seller_racf_id, 
   seller_email, wl_emailed_on, wl_survey_completed_on, wl_status, wl_Q1_answer, 
   wl_Q2_answer, wl_Q3_answer, wl_Feedback, wl_ReasonLoss
FROM dbo_temp_proposal_survey_results
WHERE DATEDIFF(day, date_proposal_completed, CAST(CONVERT(varchar(50), GETDATE(), 101) AS smalldatetime)) <= 30 
   AND wl_status IS NULL

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
For performance reasons, you never want to use a function on a table column if you can avoid it.  You can, however, manipulate the literal part of the comparison using any methods needed.  Thus, the preferred way to check whether a column date is nn days before the current date is as follows:

WHERE ([date_proposal_completed] >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 29, 0) AND
       dbo_temp_proposal_survey_results.wl_status IS NULL);

The code DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)  is a "best-practice", highly efficient way of stripping the time from the current date.  The "- 29" just backs up 29 days from the current date.

That will give you the same results, but with potentially vastly less I/O since SQL will be able to use index seeks for the above code, but not for any code involving a function on a column.

Btw, for the same reason, you should never use ISNULL() on a column in a WHERE or JOIN.
1
ZberteocCommented:
Use this:
SELECT 
    t.proposal_id, 
    t.specialist_responsible, 
    t.customer_name, 
    t.date_proposal_completed, 
    t.seller_racf_id, 
    t.seller_email, 
    t.wl_emailed_on, 
    t.wl_survey_completed_on, 
    t.wl_status, 
    t.wl_Q1_answer, 
    t.wl_Q2_answer, 
    t.wl_Q3_answer, 
    t.wl_Feedback, 
    t.wl_ReasonLoss
FROM 
    dbo_temp_proposal_survey_results t
WHERE 
    t.[date_proposal_completed]>DateAdd(DD,-30,GetDate())
    AND t.wl_status Is Null

Open in new window

0
FordraidersAuthor Commented:
Thanks to all..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.