SQL People - Corsa Sport - for Vauxhall and Opel Corsa B, Corsa C and Corsa D

Not logged in [Login - Register]

You Are Not Registered Or Not Logged In

Corsa Sport » Message Board » Off Day » SQL People

New Topic

New Poll

Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 09:55

trying to write an SQL querry to say display something if it has been modified in the last 5 days.

Select items where: (enter SQL conditions / where clause)
close Date = "17/03/2005 - 5"

Any one any ideas?

Im crap at SQL!

Half Pint

Member

Registered: 25th Mar 02
User status: Offline

17th Mar 05 at 09:56

how the fuck did you get that job

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 09:57

its some crappy tool we use and im trying to make a report work

Pablo

Member

Registered: 3rd Feb 03
Location: Milton Keynes
User status: Offline

17th Mar 05 at 09:57

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 09:58

Half Pint

Member

Registered: 25th Mar 02
User status: Offline

17th Mar 05 at 09:59

i love you really...... *MWAH* on your little sausage......

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 09:59

quote:
Originally posted by Half Pint
i love you really...... *MWAH* on your little sausage......

Now help me

PaulW

Member

Registered: 26th Jan 03
Location: Atherton, Greater Manchester
User status: Offline

17th Mar 05 at 10:00

is the closedate a value in the database which is actually in that date format??

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 10:01

its an aumatically generated date from when the item is closed.

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 10:03

ok changed it to say Last State Changed now. the last state changed date is an auto field.

PaulW

Member

Registered: 26th Jan 03
Location: Atherton, Greater Manchester
User status: Offline

17th Mar 05 at 10:03

well from what I've picked up so far...

when doing date calculations, its converted, or saved in the UNIX time format, so big-ass number as its easier to work out...

I'm still learnin this myself... and only in PHP/MySQL too!

[Edited on 17-03-2005 by PaulW]

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 10:04

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 10:43

"Last State Change Date" BETWEEN 20050317 AND 20050301

James

Member

Registered: 1st Jun 02
Location: Surrey
User status: Offline

17th Mar 05 at 10:49

SELECT items
FROM database
WHERE closedate BETWEEN 12/03/05 AND 17/03/05;

If you are planning on using the query a lot its not the best way to do it because you would need to chaneg the dates every time

James

Member

Registered: 1st Jun 02
Location: Surrey
User status: Offline

17th Mar 05 at 10:52

or you could do this

SELECT items
FROM database
WHERE closedate BETWEEN &closedate AND 17/03/05;

That would prompt you to enter the first date so you could type in what you want, needs to be entered in the correct date format tho

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 11:48

A database exception occurred, probably due to illegal syntax in the following select statement:
select count(*) from TS_CASES where (SELECT items FROM database WHERE closedate BETWEEN & closedate AND 17 / 03 / 05 ; ) and TS_CASES.TS_PROJECTID in (44,45,46) and TS_CASES.TS_ID > 0 and ((TS_CASES.TS_PROJECTID in (44,45,46)))

Syntax error. in query expression '(SELECT items FROM database WHERE closedate BETWEEN & closedate AND 17 / 03 / 05 ; ) and TS_CASES.TS_PROJECTID in (44,45,46) and TS_CASES.TS_ID > 0 and ((TS_CASES.TS_PROJECTID in (44,45,46)))'.

Ive used convert, between you name it ive used it

PaulW

Member

Registered: 26th Jan 03
Location: Atherton, Greater Manchester
User status: Offline

17th Mar 05 at 11:55

unix time is the time, in seconds, from 01/01/1970 i think... so the date is however many seconds has passed since then!

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 11:57

quote:
Originally posted by PaulW
unix time is the time, in seconds, from 01/01/1970 i think... so the date is however many seconds has passed since then!

Tried all that. putting how many minutes there is in 5 days putting the time down including seconds

Got one of the nerds here helping me and hes stumped.

Its not accepting quite a lot of things

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 11:57

I could hold an audtion ive got that many CAST's at the mo

Tim

Site Administrator

Registered: 21st Apr 00
User status: Offline

17th Mar 05 at 12:23

First we need to know what's stored in those data columns so we know what data we're dealing with...

I assume by the sort of queries you're trying to do you're using Oracle?

So...

select * from TS_CASES where rownum = 1

Also, at a guess, I assume your query should be...

select count(*) from TS_CASES where TS_CASES.CLOSEDATE BETWEEN "12-MAR-2005" AND "17-MAR-2005"
and TS_CASES.TS_PROJECTID in (44,45,46) and TS_CASES.TS_ID > 0

[Edited on 17-03-2005 by Tim]

Nismo

Member

Registered: 12th Sep 02
User status: Offline

17th Mar 05 at 12:57

Just ask Jeaves

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 13:03

quote:
Originally posted by Tim
First we need to know what's stored in those data columns so we know what data we're dealing with...

I assume by the sort of queries you're trying to do you're using Oracle?

So...

select * from TS_CASES where rownum = 1

Also, at a guess, I assume your query should be...

select count(*) from TS_CASES where TS_CASES.CLOSEDATE BETWEEN "12-MAR-2005" AND "17-MAR-2005"
and TS_CASES.TS_PROJECTID in (44,45,46) and TS_CASES.TS_ID > 0

[Edited on 17-03-2005 by Tim]

A database exception occurred, probably due to illegal syntax in the following select statement:
select count(*) from TS_CASES where (select count ( * ) from TS_CASES where TS_CASES.CLOSEDATE BETWEEN 12-MAR-2005 AND 17-MAR-2005 and TS_CASES.TS_PROJECTID in ( 44 , 45 , 46 ) and TS_CASES.TS_ID > 0 ) and TS_CASES.TS_PROJECTID in (1,2,28,34,5,15,25,36,37,8,26,27,32,35,40,41,42,44,45,46,52,53) and TS_CASES.TS_ID > 0 and ((TS_CASES.TS_PROJECTID in (1,2,28,34,5,15,25,36,37,8,26,27,32,35,40,41,42,44,45,46,52,53)))

Too few parameters. Expected 2.

Ive looked in its database and its using a a number relating to 1970 or something. 9 and 10 number date field.

We have tried alsorts using other data bases and they work fine but the same lines on this doesnt work!

TNM

Member

Registered: 5th Apr 04
Location: Nottingham Drives: VW Tiguan
User status: Offline

17th Mar 05 at 13:05

"Last State Change Date" = convert(datetime, convert(int, CURRENT_TIMESTAMP)) and i get no where

Doesnt accept CONVERTor CAST. looked at time stamps and grrrr well you name it we have tried it. its a pile of poop!

New Topic

New Poll

Corsa Sport » Message Board » Off Day » SQL People

24 database queries in 0.0108211 seconds