Solved

How can I rest days to filter datetime field

Posted on 2010-08-20
4
369 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
  • 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

778 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