CODEWITHSUNDEEP
datetimessisdate-difference
d2907
d2907

Reputation: 902

SSIS - Calculate an interval of dates with datetime fields

Let's assume that I have some dates like these:

2014-01-23 14:52  (today)
2014-01-22-15:35
2014-01-21 10:35
2014-01-20 09:45
2014-01-19 17:58
2014-01-18 14:05
2014-01-17 13:22

Now I need to take into account only the 5 previous days to the current day, so for me they will be:

2014-01-22-15:35
2014-01-21 10:35
2014-01-20 09:45
2014-01-19 17:58
2014-01-18 14:05

In SSIS I wrote the next instruction in a conditional split task:

date > DATEADD("DD",-5,GETDATE()) && date < DATEADD("DD",-1,GETDATE())

But the result that I am having depends of the HOUR in which I execute the work flow.

So, for instance, if I execute it TODAY (2014-01-23) at 13:42. I am not going to seeing 2014-01-22-15:35 because it is after a complete day consider the hour (13:42) and what I need is to see all the data that have a date at any moment from yesterday.

My question is, how can I indicate that I need ALL the dates of the previous days from today at 00:00?. In other words, how can I compute this interval for ALL the hours of the previous 5 days without taking into consideration the hour of execution.

Upvotes: 0

Views: 455

Answers (1)

Jamie Paolino
Jamie Paolino

Reputation: 617

if you are doing this in SQL you can try to cast the dateTime to a date

Declare @today date = getdate()
Declare @now datetime = getdate()
Cast(@now as date) between dateadd("DD",-5,@today) AND dateadd("DD",-1,@today)

let me know if that helps?

Upvotes: 5

Related Questions