select records where the expiry date is 5 days from current date.

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

I want to write the query only returns records where the expiry date is 5 days from current date.

Posted 19-Oct-14 16:12pm

ttds

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-10-19T19:58:00

Solution 3

Try this:

SQL

DECLARE @ToDate DATETIME = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
DECLARE @FromDate DATETIME = DATEADD(dd, -5, @ToDate)

SELECT <Field_list>
FROM <TableName>
WHERE DateTimeField BETWEEN @FromDate AND @ToDate

Note: change the code to your needs

See also:
DATEADD()[^]
DATEDIFF()[^]

Posted 19-Oct-14 19:58pm

Maciej Los

Updated 19-Oct-14 19:59pm

v2

Garth J Lancaster · Answer 2 · 2014-10-19T17:00:00

Solution 1

so, what is your problem ? where are you getting stuck ?

you might want to look at DATEADD() and GETDATE() functions for helping you with the date math

Posted 19-Oct-14 17:00pm

Garth J Lancaster

RossMW · Answer 3 · 2014-10-19T17:03:00

Solution 2

Use the dateadd function in your where clause

see http://msdn.microsoft.com/en-us/library/ms186819.aspx[^]

i.e where expirydate >= dateadd("d",5,getdate())

Getdate() returns the current date. You will need to be careful of the time part of your query depending on what you are doing.

Posted 19-Oct-14 17:03pm

RossMW

Comments

aarif moh shaikh 20-Oct-14 0:11am

yes, write..