?
Solved

How can I rest days to filter datetime field

Posted on 2010-08-20
4
Medium Priority
?
379 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

765 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