[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How can I rest days to filter datetime field

Posted on 2010-08-20
4
Medium Priority
?
382 Views
Last Modified: 2012-05-10
This is the scenario:
I have an .aspx page where the user inserts the days (integer) and other 2 criteria, then I send this data to an storeprocedure to do the filter and show the data:

      @Dias int,
      @TipoEmpleo varchar(50),
      @Localidad varchar(50)
      
      
      AS

      SELECT * FROM Ofertas WHERE TipoEmpleo = @TipoEmpleo AND UbicacionEmpleo = @Localidad AND Fecha > (Fecha - @Dias)

I need help with the  DateTime field (Fecha), I need the storeprocedure filter using the data is stored into “Fecha” less the integer data is stored into @Dias.

Thank you
Regards
Search.jpg
0
Comment
Question by:aperez_eitsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 3

Expert Comment

by:jbeasle3
ID: 33488235
You do know that Fecha is always greater than Fecha - @Dias. In other words Today is always greater than Today less 3 days.
However, using that logic. I would do something like this:
SELECT * FROM Ofertas WHERE TipoEmpleo = @TipoEmpleo AND UbicacionEmpleo = @Localidad AND Fecha > DateAdd(Day, -@Dias, Fecha)
 
0
 

Author Comment

by:aperez_eitsa
ID: 33489617
I was reviewing this... I did the changes you  suggest me, but it looks than the query do nothing, the issue is in here:

Fecha > DateAdd(Day, -@Dias, Fecha)

What I saw, we are comparing "Fecha" with "Fecha" -@Dias...

what we should compare is "Today" with "Fecha" -@Dias...

so, my question now would be how to get "Today" on sql??

Regards
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 33489699
SELECT * FROM Ofertas
WHERE TipoEmpleo = @TipoEmpleo
AND UbicacionEmpleo = @Localidad
AND Fecha > dateadd(d, -@Dias, getdate())

-- returns all records where Fecha is less than @Dias days old
-- getdate() includes the time portion so if you search at Aug 22 12:30, the query is actually

AND Fecha > 'Aug 20 12:30'
0
 

Author Closing Comment

by:aperez_eitsa
ID: 33489721
Thank you, now it works....

Thank you for your help.

Regards
0

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question