%LIKE% GETDATE() query

Hi,
I'm building an ASP application in Dreamweaver MX2004 on the application the
user is aloud to inout a record. The following page has two recordsets, one
that returns the data that the user enterred in the previuos record and
another that identifies users who have requested that kind of information.
The first recordset however is where the problem lies as currently the
recordset is built based on two values - USERID and ADREFERENCE. The problem
with this is that it could return more than one record and then everything
becomes . So I've thought about adding an additional parameter which
would be and Dateposted = GETDATE() obviously this wont work though as it
will be looking for a record posted the exact time, the question is would -
Dateposted %LIKE% GETDATE work, or is therer an alternative - ideally withou
t
any complex SQL programming as i'm totaaly new to SQL
thanksYou can use DateDiff(dd, Dateposted , getdate())= 0 to do it.
Perayu
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:21F267E8-E6C6-4B78-AF25-CDB0B68B7137@.microsoft.com...
> Hi,
> I'm building an ASP application in Dreamweaver MX2004 on the application
> the
> user is aloud to inout a record. The following page has two recordsets,
> one
> that returns the data that the user enterred in the previuos record and
> another that identifies users who have requested that kind of information.
> The first recordset however is where the problem lies as currently the
> recordset is built based on two values - USERID and ADREFERENCE. The
> problem
> with this is that it could return more than one record and then everything
> becomes . So I've thought about adding an additional parameter
> which
> would be and Dateposted = GETDATE() obviously this wont work though as it
> will be looking for a record posted the exact time, the question is
> would -
> Dateposted %LIKE% GETDATE work, or is therer an alternative - ideally
> without
> any complex SQL programming as i'm totaaly new to SQL
> thanks|||You can't use the LIKE comparison operator with datetime values, but you can
use BETWEEN. For example, if you wanted to find records within the last 5
minutes
SELECT ...
WHERE [Dateposted] BETWEEN DATEADD(mi, -5, GETDATE()) AND GETDATE()
For other ideas, take a look at the DATEADD and DATEDIFF functions in Books
Online.
"GTN170777" wrote:

> Hi,
> I'm building an ASP application in Dreamweaver MX2004 on the application t
he
> user is aloud to inout a record. The following page has two recordsets, on
e
> that returns the data that the user enterred in the previuos record and
> another that identifies users who have requested that kind of information.
> The first recordset however is where the problem lies as currently the
> recordset is built based on two values - USERID and ADREFERENCE. The probl
em
> with this is that it could return more than one record and then everything
> becomes . So I've thought about adding an additional parameter whi
ch
> would be and Dateposted = GETDATE() obviously this wont work though as it
> will be looking for a record posted the exact time, the question is would
-
> Dateposted %LIKE% GETDATE work, or is therer an alternative - ideally with
out
> any complex SQL programming as i'm totaaly new to SQL
> thanks|||> The first recordset however is where the problem lies as currently the
> recordset is built based on two values - USERID and ADREFERENCE. The
> problem
> with this is that it could return more than one record and then everything
> becomes .
If you are using IDENTITY then after the insert you could select
SCOPE_IDENTITY() and keep track of that. Now, just
SELECT UserID, AdReference, DatePosted
FROM your_table
WHERE identity_column = whatever;
If you are not using IDENTITY and the key is UserID/AdReference/DateTime
then you could say:
SELECT TOP 1 UserID, AdReference, DatePosted
FROM your_table
ORDER BY DatePosted DESC;

> the question is would -
> Dateposted %LIKE% GETDATE work
No, there are all kinds of issues with this. First off, GETDATE() returns a
date, not a string. What you see when you SELECT GETDATE() or PRINT is not
how it's stored internally. So, you'd have to convert both sides to a
string, and this is not going to work well at all. Additionally, GETDATE()
when you run the select afterward is not going to contain the same value as
GETDATE() was when the insert happened. You could restrict the comparison
to the hour or to the day, but if they added two posts in that timeframe,
you would still get multiple rows back.
A|||"Perayu" wrote:
> You can use DateDiff(dd, Dateposted , getdate())= 0 to do it.
> Perayu
>
--The problem than can happen with this is
SELECT DATEDIFF(dd, '2006-01-24 23:59:59', '2006-01-25 00:00:01')
/*Returns 1, even though the two dates are only 2 seconds apart. Not a bug;
DATEDIFF counts the number of dividing boundaries crossed going from the
first date to the second date for the specified type of date differential */|||Thanks Mark, should have thought about that!!
"Mark Williams" wrote:
> You can't use the LIKE comparison operator with datetime values, but you c
an
> use BETWEEN. For example, if you wanted to find records within the last 5
> minutes
> SELECT ...
> WHERE [Dateposted] BETWEEN DATEADD(mi, -5, GETDATE()) AND GETDATE()
> For other ideas, take a look at the DATEADD and DATEDIFF functions in Book
s
> Online.
> --
>
> "GTN170777" wrote:
>|||But what if they had multiple posts in the last 5 minutes? You should be
designing your system so that you can identify a single row by more than the
fact that it fell within a certain time range. No matter how narrow you
make your window, it is possible that either (a) multiple rows will match or
(b) your window is too small to even capture the last insert.
> Thanks Mark, should have thought about that!!
> "Mark Williams" wrote:
>|||To compare datetime fields and ignore the time, do this
WHERE CONVERT(CHR(8), Dateposted ,112) = CONVERT(CHAR(8),GetDate(),112)
this would be the same as the following if Dateposted = 1/24/2006
WHERE '20060124' = '20060125'
But this probably would not solve your problem if the user could add more
than one record per day. The best solution is to use SCOPE_IDENTITY() as a
previous poster suggested.
kevin
"GTN170777" wrote:

> Hi,
> I'm building an ASP application in Dreamweaver MX2004 on the application t
he
> user is aloud to inout a record. The following page has two recordsets, on
e
> that returns the data that the user enterred in the previuos record and
> another that identifies users who have requested that kind of information.
> The first recordset however is where the problem lies as currently the
> recordset is built based on two values - USERID and ADREFERENCE. The probl
em
> with this is that it could return more than one record and then everything
> becomes . So I've thought about adding an additional parameter whi
ch
> would be and Dateposted = GETDATE() obviously this wont work though as it
> will be looking for a record posted the exact time, the question is would
-
> Dateposted %LIKE% GETDATE work, or is therer an alternative - ideally with
out
> any complex SQL programming as i'm totaaly new to SQL
> thanks|||That sounded interesting, so I added IsLike(pattern) to my TDate UDT at
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
Here is sample of usage:
DECLARE @.t table(
Date datetime
);
insert @.t SELECT '1/1/2005'
insert @.t select '1/2/2005'
insert @.t select '9/1/2005'
insert @.t select '4/4/2005'
insert @.t select '4/12/2005'
insert @.t select '10/20/2005'
insert @.t select '11/22/2005'
insert @.t select '12/25/2005'
-- Select any date in 2005 that has a day in the 20's.
select Date
from @.t
where TDate::FromSqlDateTime(Date).IsLike(''/2?/2005') = 1
-- Select any date that only has 1 digit in the month and 1 digit in the
day.
select Date
from @.t
where TDate::FromSqlDateTime(Date).IsLike('?/?/'') = 1
OUTPUT
Date
2005-10-20 00:00:00.000
2005-11-22 00:00:00.000
2005-12-25 00:00:00.000
Date
2005-01-01 00:00:00.000
2005-01-02 00:00:00.000
2005-09-01 00:00:00.000
2005-04-04 00:00:00.000
Kinda .
--
William Stacey [MVP]
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:21F267E8-E6C6-4B78-AF25-CDB0B68B7137@.microsoft.com...
| Hi,
| I'm building an ASP application in Dreamweaver MX2004 on the application
the
| user is aloud to inout a record. The following page has two recordsets,
one
| that returns the data that the user enterred in the previuos record and
| another that identifies users who have requested that kind of information.
| The first recordset however is where the problem lies as currently the
| recordset is built based on two values - USERID and ADREFERENCE. The
problem
| with this is that it could return more than one record and then everything
| becomes . So I've thought about adding an additional parameter
which
| would be and Dateposted = GETDATE() obviously this wont work though as it
| will be looking for a record posted the exact time, the question is
would -
| Dateposted %LIKE% GETDATE work, or is therer an alternative - ideally
without
| any complex SQL programming as i'm totaaly new to SQL
|
| thanks