[SGVLUG] Can SQL even do this?

[Emerson, Tom (*IC)]

Well, the structure can be updated to accommodate the problem :)
 
for the most part, what I described is the structure and problem I'm
trying to solve
 
  one [arbitrary] table with a field of type DATE* that contains the
first "event"
 
  one table with a NUMERIC field indicating which WEEK this event
unfolds, where "1" == the date indicated in the first table  (this could
be changed so that "0" indicates the first event)
 
  one table with a single DATE* field containing days that events should
NOT occur -- in which case that event and all subsequent events should
occur one week later [unless, of course, that date is also in this
table]
 
* DATE* can be DATE, DATETIME, TIMESTAMP, or any other suitable field
type that would store a "date" as a minimum.
 
This should be as database-neutral as possible :)
 
Any chance you could make it to Devsig this saturday?  Might make for a
lively discussion ...
 

-----Original Message-----
From: sgvlug-bounces at sgvlug.net [mailto:sgvlug-bounces at sgvlug.net] On
Behalf Of Solomon K. Chang
Sent: Wednesday, October 29, 2008 12:52 AM
To: SGVLUG Discussion List.
Subject: Re: [SGVLUG] Can SQL even do this?


Well, that's MySQL-specific.  Thom hasn't yet said what flavor of SQL
he's using.

Personally, as someone who's had to do plenty of weekly date calcs from
SQL syntax, yes, date_add and date_sub are your friends.  When you need
to render DATE data types into a humanly readable format, use
date_format.

As someone who claims to be the premier MySQL guru of Southern
California, I'll take on your problem in full stride.  Send me the
structure of your tables and a description of what you're trying to
do...


Solomon Chang


--- On Tue, 10/28/08, Alan Horn <ahorn at deorth.org> wrote:



From: Alan Horn <ahorn at deorth.org>
Subject: Re: [SGVLUG] Can SQL even do this?
To: "SGVLUG Discussion List." <sgvlug at sgvlug.net>
Date: Tuesday, October 28, 2008, 8:37 PM


On Tue, 28 Oct 2008, John E. Kreznar wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> "Emerson, Tom \(*IC\)"
<Tom.Emerson at wbconsultant.com> writes:
>
>> I've got an "interesting" problem -- ... I'm not
even sure how to
>> convert "11/02/2008 + 3" to get "11/16/2008" ...
>

A quick net search turns up this :
http://www.devshed.com/c/a/MySQL/Date-Arithmetic-With-MySQL/3/

mysql has the DATE_ADD and DATE_SUB functions.

They do what you would expect.

Cheers,

Al



-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.sgvlug.net/pipermail/sgvlug/attachments/20081029/30d52ece/attachment-0001.html