Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Verbiage: SQL Server restriction due to Oracle Designer

Chacham (981) writes | more than 4 years ago

PC Games (Games) 2

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 f

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.

Sorry! There are no comments related to the filter you selected.

More "SQL in the real world" annoyance (1)

Qzukk (229616) | more than 4 years ago | (#30854282)

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 "less specific" field, so that '2009-01' = '2009-01-31 23:59:59.999'. More (possibly a lot more) thought is needed (i.e., what's '2008' + '1 second'? + '366 days'? What about timezones? Is '2009-01' really BETWEEN '2009-01-31' AND '2009-02-01'? Is '2009' between them?)

Another solution would be to typecast all datetime comparisons in order to specify the exact granularity desired. '2009-01-31' is always BETWEEN '2009-01-01' and '2009-01-31', even if it used to be 3PM before the conversion. Not sure about Oracle, but to compare just months and years, Postgres has a date_trunc() function for dates, but it truncates to a datetime at the start of that period, ie date_trunc('month','2009-01-15'::date) is '2009-01-01 00:00:00-06' I suspect this would ignore indexes unless you went back and reindexed with the date_trunc() function.

Re:More "SQL in the real world" annoyance (1)

Chacham (981) | more than 4 years ago | (#30856582)

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

Check for New Comments
Slashdot Login

Need an Account?

Forgot your password?