reset password
Author Message
JackStrauss
Posts: 236
Posted 19:56 May 18, 2012 |

.

Last edited by JackStrauss at 21:34 Mar 27, 2014.
redge
Posts: 31
Posted 20:09 May 18, 2012 |

As I understand it, all she means is that your query should compare the publish dates of the books to (current year - 12) instead of the year 2000. If you specify 2000, then in a year your query will return all books published in May of the past 13 years. Using a (current year - 12) function, however, will return books published 12 years before the date the query is run. Example: running a query with a hardcoded date in 2050 will return all books published in the past 50 years; a dynamic function (current year - 12) would return all books published between 2038 and 2050.

 

For those of us who hate reading: the posted queries are fine as they do not specify the year 2000.

 

On an unrelated note, I strongly recommend using line breaks in your queries

SELECT data

FROM table1

JOIN table2

WHERE (first clause)

AND (second clause)

ORDER BY (criteria);

Or something to that extent. I think you'll find it makes it a lot easier to read and keep track of (both for you and anyone else reading it), especially on particularly complicated queries.

JackStrauss
Posts: 236
Posted 20:13 May 18, 2012 |

.

Last edited by JackStrauss at 21:35 Mar 27, 2014.
redge
Posts: 31
Posted 20:17 May 18, 2012 |

You're welcome.

It took me some particularly scary PostgreSQL work to learn to use line breaks and indentation properly. To demonstrate:

 

UPDATE product SET cover_sort_date = 
(CASE
-- All below cases reformat dates as YYYY-MM-DD
-- Searches for all dates formatted Month Day Year or Month Day, Year
WHEN date_edit ~ '([\\w]{3,})\\s+([0-9]{1,2}),*\\s+([0-9]{4})' THEN to_date(regexp_replace(date_edit, '([\\w]+)\\s+([0-9]{1,2}),*\\s+([0-9]{4})', '\\1 \\2, \\3'), 'Month DD, YYYY')
-- Searches for all dates missing a day
WHEN date_edit ~ '([\\w]{3,})\\s+([0-9]{4})' THEN to_date(regexp_replace(date_edit, '(?:Test\\s)*([\\w]+)(?:/\\w+)*\\s+([0-9]{4}).*$', '\\1 01, \\2'), 'Month DD, YYYY')
-- Searches for all dates with two days, separated by &, dropping the second day
WHEN date_edit ~ '([\\w]{3,})\\s+([0-9]{1,2})(\\s&\\s[0-9]{2})*,*\\s+([0-9]{4})' THEN to_date(regexp_replace(date_edit, '([\\w]+)\\s+([0-9]{1,2})(?:\\s&\\s[0-9]{2})*,*\\s+([0-9]{4})', '\\1 \\2, \\3'), 'Month DD, YYYY')
END)
WHERE dtype = 'Issue';
george666
Posts: 1
Posted 10:31 May 20, 2012 |
All the books that have more than 12 years old (from the day they where published until now) have been excluded.
 
select title_id
from Titles
where month(pubdate)=5 and (year(now())-year(pubdate))<12; 
 
dates are much easier to work with if you covert them in years.
Good luck!