I hate databases so much

Endurance Man

Need a bit of help, creating a query which has a an attribute called End date. This has a bunch of dates which show when projects where completed. The query needs to just show projects that where completed in the last 18months.
I know i could just work out what the date was 18months ago and then go >"that date" but i would have to update that every day.
Help .

PS: Databases is the worst bloody subject in the history of man feckin kind!

JackieChan

EM,
All databases have a function that return the current date. For example in Oracle this is sysdate.
So your query could be select c1,c2,c3 from table where end_date>sysdate-(1.5*365).

PS: Databases is the worst bloody subject in the history of man feckin kind!

DBs are what I make my money from so I'll not hear anything bad said against them!!!

The Corinthian

I suggest you Google for date/time functions for the database in question, then pick the most relevant one to make an on-the-fly 18 month differential calculation.

Endurance Man

Ye im doing this in Access. I know there is a function like this but cant bloody remember it.

Webmonkey

This any help?:

http://personal-computer-tutor.com/abc2/v13/james13.htm

Endurance Man

Webmonkey wrote:

This any help?:

http://personal-computer-tutor.com/abc2/v13/james13.htm

Ye those are the functions, but i cant bloody work out how to word this thing. This subject has really been rushed on my course .

cyberbob

datediff( "d" , nz([end date],now() ) , now() )

is the expression i imagine you want and stick in the criteria that this is less than 30.4 * 12 ...

(average month)


( I make my money from Access Databases so I'm with Jackie on that one )

The Corinthian

Endurance Man wrote:

Ye those are the functions, but i cant bloody work out how to word this thing.

You'll have to be more specific with what's giving you trouble, otherwise you're simply asking people to do your homework for you.

rogue-entity

Offtopic but:
Access databases are sh1te, and I have to use them in college (give me MySQL any day).

Google is your friend OP.

Tom Dunne

Endurance Man wrote:

PS: Databases is the worst bloody subject in the history of man feckin kind!

Wash your mouth out.

The function you are looking for is >NOW()-(1.5*365)

The problem is that you are not using a real database.

scojones

If this little issue is annoying you this much then I'd reconsider the path you are going down.

Webmonkey

I'm sure he doesn't really mean it

bonkey

tom dunne wrote:

The function you are looking for is >NOW()-(1.5*365)

The problem is that you are not using a real database.

I have to giggle a the implication that a real database would use 365 days as a year, or that 547.5 days equals 18 months.

Endurance Man

Webmonkey wrote:

I'm sure he doesn't really mean it

I really do :eek: , seriously though i think its down to my lecturer and the way we are being taught all of this, its just being rushed. Its only a small part of my course, we don't do it next year thank mary.
I may have solved the problem though, im gonna trade one of the girls in my class her DB assignment for my programming assignment . Im gonna work on it bit more tonight but i don't hold out much hope.
Thanks for all the reply, ill give these functions a go.

Graham

Go and check out DateAdd function (you can add a negative number) then combone this with a function that returns the current date.

bonkey

Endurance Man wrote:

I may have solved the problem though, im gonna trade one of the girls in my class her DB assignment for my programming assignment .

Great solution.

Its not like you'll need database programming in the real world.

Ice_Box

In oracle you would do

select * from some_table where some_date >= add_months(sysdate,-18)

Nalz

I hate sql server enterprise manager



yes, yes I do

bonkey

Ice_Box wrote:

In oracle you would do

select * from some_table where some_date >= add_months(sysdate,-18)

While you're strictly correct, I'd also trunc sysdate.

Clarification:

sysdate returns date-and-time. As I write this, it is 15:14 (local time), December 12, 2006.

18 months ago, it was 15:14, June 12, 2005.


The question is whether or not something happening at (say) 15:00, 12:00, 08:00 or even 0:01 on June 12, 2005 should still count as being 18 months ago.

If all of those are, then we ask the same about events on June 11, 2005.

Put differently, we need to determine what level of granularity we want.

Generally, people want daily granularity (at least). Its possible they want monthly. So you might need to trunc the sysdate to the required granularity.

kincsem

Whatever happened to the old way of solving problems - getting stuck in until you solve it yourself. Its not the answer you need, its the methods / craft learned from hard graft.

When I was a lad .... yada, yada, yada ...........

The Corinthian

kincsem wrote:

Whatever happened to the old way of solving problems - getting stuck in until you solve it yourself. Its not the answer you need, its the methods / craft learned from hard graft.

There are plenty of people who have a genuine interest in IT and instinctively want to understand and work out these issues on their own. However every year you will get thousands of new graduates who got through college by copying and pasting other people’s code and every year they either find themselves having to catch up in their first jobs or they find themselves in text support because they’re quickly filtered out of any serious positions when applying for jobs.

Endurance Man

The Corinthian wrote:

There are plenty of people who have a genuine interest in IT and instinctively want to understand and work out these issues on their own. However every year you will get thousands of new graduates who got through college by copying and pasting other people’s code and every year they either find themselves having to catch up in their first jobs or they find themselves in text support because they’re quickly filtered out of any serious positions when applying for jobs.

As i mentioned this is a tiny part of my course, we only do 3months of it thankfully. I have no desire what so ever to go into the database side of things. Im flying along with the programming though, thats what i wanted to do in the first place :cool: .

Webmonkey

If you going to do programming have fun trying to avoid databases. I would imagine they will creep up somewhere. To be honest a programmer should be comfortable with databases.

The Corinthian

Endurance Man wrote:

As i mentioned this is a tiny part of my course, we only do 3months of it thankfully. I have no desire what so ever to go into the database side of things. Im flying along with the programming though, thats what i wanted to do in the first place :cool: .

As Webmonkey mentioned you're not going to get terribly far in programming before coming across databases. Of course that does not mean you should be as proficient as a DBA either, but if you're looking to work in programming jobs that involve no DB work at all, then you've just cut out at least 95% of the market.

Evil Phil

Yep. Knowing your way around a DB and SQL is vital in this job. As TC said you don't have to be a DBA but you're not going to be very useful to prospective employers if you don't have these skills. Persevere and it will become clearer - and I would keep it up after the module ends so you stay fresh. It will also be a huge benefit come project time.

Merrion

You can use the DateDiff function with "m" in the first parameter to specify the date difference in months...

bonkey

The Corinthian wrote:

but if you're looking to work in programming jobs that involve no DB work at all, then you've just cut out at least 95% of the market.

I was just talking to someone about this recently.

When I finished uni, most IT graduates were happy to get a job. In an ideal world, they'd get a job in a language they liked, and that language would probably be C or C++. But first and foremost, getting a programming job was as good as it got.

A few years later, this had changed. College graduates all wanted to be either Web developers or Java developers. Full stop. End of story. If it wasn't cool and cutting edge, it was for those old-and-past-it 40-year-old fogeys who wouldn't know real programming if it hit them in the face.

In recent years, I've noticed a trend away from even this, and a surprisingly large number of college graduates all want to be games developers. Failing that, Web 2.0 will probably do at a stretch. Cause games developing is cool and cutting edge, and Web 2.0 is at least fully buzzword compliant if not also cool and cutting edge. Java, on the other hand, is now so easy to find coders for that its commodotised. Any Tom, Dick or Harry is expected to know it, and payscales are beginning to reflect that. work on cutting edge stuff, and you can be paid like a rockstar and work with the coolest stuff.

Everyone wants to be a rockstar, and lets face it....databases and rockstars have nothing in common.

Me...I'll let them. My contract rates are going up next year because its so damned hard to find a replacement who can and will do database work and maintain a reporting system.

I don't have to get better...just rarer...and hey, I don't even have to work hard to become rarer. I just have to stay in the game.

So please, college students....stick with it. Be rockstars. Avoid learning business-useful stuff like databasing.

You're earning me a fortune.

scojones

Nice one bonkey.

Evil Phil

Where and how does all this new fangelry keep and retrieve it's data? Or have I said to much ...

Seth Brundle

Its magic!

scojones

They all hire bonkey.