Query Trouble

[Sproul]

I'm having a bit of trouble creating a query
I've got an existing query which i use to search a database for results for
the current user, what i require it to do is search for results that appeared
in the last 12 hours.
I'm currently using this

datediff('h',Timestamp,Date())<12

but it also shows entries from other days.

Does anyone know a way that i can show results from the previous 12 hours
from the time of search, it would also need to take in to acount that it
could be a search a 1am therefore searching over 2 dates

Timestamp is in the format of MM/DD/YYYY hh:mm:ss.


Al....

 

[Kevin Spencer]

Tey putting the identifier "Timestamp" in [square brackets]. "Timestamp" is
a reserved word in SQL Server.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

If the truth hurts, wear it.

 

[Sproul]

I'll give it a go, but i dont think that in this instance Timestamp is the
issue, i think it's something to do with only looking at the hour, i need it
to look at the whole date and take 12 hours off it.
To verify your theory i'll change time stamp to another word "TSinfo" is
what i've used before.

Thanks

Al...

Tey putting the identifier "Timestamp" in [square brackets]. "Timestamp" is
a reserved word in SQL Server.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

If the truth hurts, wear it.

I'm having a bit of trouble creating a query
I've got an existing query which i use to search a database for results
for
the current user, what i require it to do is search for results that
appeared
in the last 12 hours.
I'm currently using this

datediff('h',Timestamp,Date())<12

but it also shows entries from other days.

Does anyone know a way that i can show results from the previous 12 hours
from the time of search, it would also need to take in to acount that it
could be a search a 1am therefore searching over 2 dates

Timestamp is in the format of MM/DD/YYYY hh:mm:ss.


Al....

 

[Sproul]

OK i've replaced Timestamp with TSinfo, no difference
However a bit more investigating shows the following

if now is 24/05/2006 14:51:47
Using

datediff('h',TSinfo,Date())<12

in the query still allows me to see records dated 23/05/2006 14:51:47
but doesn't show records dated 22/05/2006 14:51:47

don't know if it helps, but definately strange.

just a guess but is it maybe something to do with Date() should i be using
something else?.

Al....

Tey putting the identifier "Timestamp" in [square brackets]. "Timestamp" is
a reserved word in SQL Server.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

If the truth hurts, wear it.

I'm having a bit of trouble creating a query
I've got an existing query which i use to search a database for results
for
the current user, what i require it to do is search for results that
appeared
in the last 12 hours.
I'm currently using this

datediff('h',Timestamp,Date())<12

but it also shows entries from other days.

Does anyone know a way that i can show results from the previous 12 hours
from the time of search, it would also need to take in to acount that it
could be a search a 1am therefore searching over 2 dates

Timestamp is in the format of MM/DD/YYYY hh:mm:ss.


Al....

 

[Kevin Spencer]

Hi Al,

I woulsd still receommend putting the square brackets around the table name,
but I just had the time to check your DateDiff syntax, and it is not
correct. Here is the correct format for SQL Server:

DateDiff(hh, [TimeStamp], GetDate())

However, I also just noticed that you didn't specify what database you're
using, and the syntax looks like Access. In that case:

DateDiff('h', [TimeStamp], Date())

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

If the truth hurts, wear it.

I'll give it a go, but i dont think that in this instance Timestamp is the
issue, i think it's something to do with only looking at the hour, i need
it
to look at the whole date and take 12 hours off it.
To verify your theory i'll change time stamp to another word "TSinfo" is
what i've used before.

Thanks

Al...

Tey putting the identifier "Timestamp" in [square brackets]. "Timestamp"
is
a reserved word in SQL Server.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

If the truth hurts, wear it.

I'm having a bit of trouble creating a query
I've got an existing query which i use to search a database for results
for
the current user, what i require it to do is search for results that
appeared
in the last 12 hours.
I'm currently using this

datediff('h',Timestamp,Date())<12

but it also shows entries from other days.

Does anyone know a way that i can show results from the previous 12
hours
from the time of search, it would also need to take in to acount that
it
could be a search a 1am therefore searching over 2 dates

Timestamp is in the format of MM/DD/YYYY hh:mm:ss.


Al....

 

[Stefan B Rusynko]

Guess you need to learn the difference between
Data() - does not return a time
and
Now() - does return a time

And how the datediff works (for hours vs. minutes vs. seconds)

The correct comparison is

datediff("s",TSinfo,Now())<12*60*60

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


| OK i've replaced Timestamp with TSinfo, no difference
| However a bit more investigating shows the following
|
| if now is 24/05/2006 14:51:47
| Using
|
| datediff('h',TSinfo,Date())<12
|
| in the query still allows me to see records dated 23/05/2006 14:51:47
| but doesn't show records dated 22/05/2006 14:51:47
|
| don't know if it helps, but definately strange.
|
| just a guess but is it maybe something to do with Date() should i be using
| something else?.
|
| Al....
|
| "Kevin Spencer" wrote:
|
| > Tey putting the identifier "Timestamp" in [square brackets]. "Timestamp" is
| > a reserved word in SQL Server.
| >
| > --
| > HTH,
| >
| > Kevin Spencer
| > Microsoft MVP
| > Professional Numbskull
| >
| > If the truth hurts, wear it.
| >
| > | > > I'm having a bit of trouble creating a query
| > > I've got an existing query which i use to search a database for results
| > > for
| > > the current user, what i require it to do is search for results that
| > > appeared
| > > in the last 12 hours.
| > > I'm currently using this
| > >
| > > datediff('h',Timestamp,Date())<12
| > >
| > > but it also shows entries from other days.
| > >
| > > Does anyone know a way that i can show results from the previous 12 hours
| > > from the time of search, it would also need to take in to acount that it
| > > could be a search a 1am therefore searching over 2 dates
| > >
| > > Timestamp is in the format of MM/DD/YYYY hh:mm:ss.
| > >
| > >
| > > Al....
| >
| >
| >

 

[Sproul]

Thanks Stefan, i had just figured it out but had problems with one rouge
field, as i was changing the data in the database to see if it worked ok, i
had to run the database compact and repair function to sort the error.

datediff('h',TSinfo,Now())<12

was the final result, as long as the database has no errors.

Thanks to both of you, just shows sometimes the error is staring you in the
face.


Al....

Guess you need to learn the difference between
Data() - does not return a time
and
Now() - does return a time

And how the datediff works (for hours vs. minutes vs. seconds)

The correct comparison is

datediff("s",TSinfo,Now())<12*60*60

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


| OK i've replaced Timestamp with TSinfo, no difference
| However a bit more investigating shows the following
|
| if now is 24/05/2006 14:51:47
| Using
|
| datediff('h',TSinfo,Date())<12
|
| in the query still allows me to see records dated 23/05/2006 14:51:47
| but doesn't show records dated 22/05/2006 14:51:47
|
| don't know if it helps, but definately strange.
|
| just a guess but is it maybe something to do with Date() should i be using
| something else?.
|
| Al....
|
| "Kevin Spencer" wrote:
|
| > Tey putting the identifier "Timestamp" in [square brackets]. "Timestamp" is
| > a reserved word in SQL Server.
| >
| > --
| > HTH,
| >
| > Kevin Spencer
| > Microsoft MVP
| > Professional Numbskull
| >
| > If the truth hurts, wear it.
| >
| > | > > I'm having a bit of trouble creating a query
| > > I've got an existing query which i use to search a database for results
| > > for
| > > the current user, what i require it to do is search for results that
| > > appeared
| > > in the last 12 hours.
| > > I'm currently using this
| > >
| > > datediff('h',Timestamp,Date())<12
| > >
| > > but it also shows entries from other days.
| > >
| > > Does anyone know a way that i can show results from the previous 12 hours
| > > from the time of search, it would also need to take in to acount that it
| > > could be a search a 1am therefore searching over 2 dates
| > >
| > > Timestamp is in the format of MM/DD/YYYY hh:mm:ss.
| > >
| > >
| > > Al....
| >
| >
| >

 

[Stefan B Rusynko]

That will not give you 12 hrs
- it will result in 12 +/-~1 hr
--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


| Thanks Stefan, i had just figured it out but had problems with one rouge
| field, as i was changing the data in the database to see if it worked ok, i
| had to run the database compact and repair function to sort the error.
|
| datediff('h',TSinfo,Now())<12
|
| was the final result, as long as the database has no errors.
|
| Thanks to both of you, just shows sometimes the error is staring you in the
| face.
|
|
| Al....
|
| "Stefan B Rusynko" wrote:
|
| > Guess you need to learn the difference between
| > Data() - does not return a time
| > and
| > Now() - does return a time
| >
| > And how the datediff works (for hours vs. minutes vs. seconds)
| >
| > The correct comparison is
| >
| > datediff("s",TSinfo,Now())<12*60*60
| >
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > To find the best Newsgroup for FrontPage support see:
| > http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
| > _____________________________________________
| >
| >
| > | OK i've replaced Timestamp with TSinfo, no difference
| > | However a bit more investigating shows the following
| > |
| > | if now is 24/05/2006 14:51:47
| > | Using
| > |
| > | datediff('h',TSinfo,Date())<12
| > |
| > | in the query still allows me to see records dated 23/05/2006 14:51:47
| > | but doesn't show records dated 22/05/2006 14:51:47
| > |
| > | don't know if it helps, but definately strange.
| > |
| > | just a guess but is it maybe something to do with Date() should i be using
| > | something else?.
| > |
| > | Al....
| > |
| > | "Kevin Spencer" wrote:
| > |
| > | > Tey putting the identifier "Timestamp" in [square brackets]. "Timestamp" is
| > | > a reserved word in SQL Server.
| > | >
| > | > --
| > | > HTH,
| > | >
| > | > Kevin Spencer
| > | > Microsoft MVP
| > | > Professional Numbskull
| > | >
| > | > If the truth hurts, wear it.
| > | >
| > | > | > | > > I'm having a bit of trouble creating a query
| > | > > I've got an existing query which i use to search a database for results
| > | > > for
| > | > > the current user, what i require it to do is search for results that
| > | > > appeared
| > | > > in the last 12 hours.
| > | > > I'm currently using this
| > | > >
| > | > > datediff('h',Timestamp,Date())<12
| > | > >
| > | > > but it also shows entries from other days.
| > | > >
| > | > > Does anyone know a way that i can show results from the previous 12 hours
| > | > > from the time of search, it would also need to take in to acount that it
| > | > > could be a search a 1am therefore searching over 2 dates
| > | > >
| > | > > Timestamp is in the format of MM/DD/YYYY hh:mm:ss.
| > | > >
| > | > >
| > | > > Al....
| > | >
| > | >
| > | >
| >
| >
| >