Chacham's Journal: Verbiage: SQL Server restriction due to Oracle Designer

One of the things that bother me about BETWEEN in SQL is how it handles dates. Being BETWEEN is inclusive, BETWEEN with dates is a pain. If i want to return all records from this week, it has to say WHERE date-col >= beginning-of-week AND date-col beginning-of-next-week. The reason is that dates can includes times, which means the second boundary of a week is the very last moment of the week. But granularity of DATE can change in between database versions, so, to be accurate, we use the first moment that is not wanted. This means BETWEEN cannot be used. This is particularly problematic when a date is calculated. It now must be calculated twice. Once for each side. Sometimes this is as easy as adding 7 days, sometimes the range is more difficult.

On the project i am currently on, we have a TABLE that records period, where each period is a month. So, there's year and month COLUMNs, and this work nicely for what we need. But queries started becoming tedious when dates had to be matched in each period. Turn it into a date, then get the other date, finally use it.

To help, i asked the DA to give me two COLUMNs, and he suggested they be called effective in and effective out. Good enough. I wanted to DEFAULT the COLUMNs based on the year and month, but SQL Server won't alow a DEFAULT to refer to another COLUMN. So, make it a generated COLUMN, for which i supplied the code.

I got a callback from the DA stating he couldn't do it. Why not? The DAs use Oracle Designer for the data model, and hacked it to support SQL Server. Well, they didn't add support for generated COLUMNs, which means the produced script would have to be edited each time, meaning the model and the actual schema would be out of sync.

Ugh, now it's either A VIEW or a TRIGGER. The VIEW adds convenience, but calculates the same number again and again. TRIGGERs are just bad. He liked the VIEW because what i wanted was the convenience and i was concerned for a performance problem that might not exist. So, i said go with the VIEW. After hanging up the phone i called back and changed to COLUMNs. The re-calculation bothers me too much.

More "SQL in the real world" annoyance

[Qzukk]

Your problem might best be solved by someone inventing a "partial datetime" type that could store, well, any part of a datetime starting with the year, as well as a "significant digits" that internally tracked whether you entered '2009-01' or '2009-01-01 00:00:00.000'. This type would then be given consistent behavior for typecasting as well as operations with other date/time types. For instance, comparison with another full or partial datetime could be achieved by casting the "more specific" field to the

Re:

[Chacham]

Unfortunately, they would both negate the use of the any INDEXes.