3

Here's my problem. My system stores dates and times in your usual DATETIME format:

'YYYY-MM-DD HH:MM:SS'

This is what I have trouble with:

I need to select all the contacts that have a date field in this format:

'XXXX-12-02 23:59:59' and every other date 7 days leading up to it.

For example, I would need to get all these rows with these dates in response:

1965-12-02
1985-11-28
1990-12-01

Is this possible and if it is, any help or tips that you can give me?

kingmaple
  • 4,200
  • 5
  • 32
  • 44

2 Answers2

0

You can get a specific part of your DATETIME variable using the following functions.

  • DAY(your_DATETIME_Variable) --> gets day as integer
  • MONTH(your_DATETIME_Variable) --> gets month as integer
  • YEAR(your_DATETIME_Variable) --> gets year as integer
frogatto
  • 28,539
  • 11
  • 83
  • 129
  • That's easy as long as the day of month > 7, but the days the OP is looking for may be in another month or even another year than the "seed-date" and then things starts to get a bit messy to do in SQL... – MortenSickel Nov 27 '12 at 08:50
  • This is what my colleague suggested, but it doesn't work exactly for the reason where months (or years) change. – kingmaple Nov 27 '12 at 08:52
0

The easy part: To find any date on the same day and month:

SELECT .... FROM .... WHERE 
   month(timefield)=month('2012-12-02 23:59:59') 
   and day(timefield) = day('2012-12-02 23:59:59)

A messy way of doing it, but it will (mostly) work is to do a

SELECT .... FROM .... WHERE 
   (month(timefield)=month('2012-12-02 23:59:59') 
   and day(timefield) = day('2012-12-02 23:59:59')) or
   (month(timefield)=month(date_sub('2012-12-02 23:59:59' interval 1)) 
   and day(timefield) = day(date_sub('2012-12-02 23:59:59') interval 1)) or
   (month(timefield)=month(date_sub('2012-12-02 23:59:59' interval 2)) 
   and day(timefield) = day(date_sub('2012-12-02 23:59:59') interval 2)) or

and so on...

Then the problem comes up: What with leap years... I do not have any good solutions for that... If e.g your seed date is 05-mar-2012, then you will only get back to 28-feb-2012, but I guess you want the data back to 27 feb 2011... One possible solution to that is to make sure that you always normalize the date to a leap year, fetch the days 8 days back and throws away what you do not want in the front end.

MortenSickel
  • 2,118
  • 4
  • 26
  • 44