Database Query

[John]

Hi,
Here is the SQL.

select left(d.batchstartdatetime,11) as
BatchDate,d.batchseqnum,d.envelopeseq,d.tendertypecode,d.te
ndercode,e.templatekey from debit d, envelope e where
d.batchstartdatetime>'8/5/2004' and
d.batchstartdatetime<'8/6/2004' and d.tendertypecode in
('ww','wg')and e.templatekey=1 and
d.batchstartdatetime=e.batchstartdatetime and
d.batchseqnum=e.batchseqnum and d.envelopeseq=e.envelopeseq

I know that this works in MS SQL. What I am trying to get
it to do is to run though a web page. What I want to
happen is for the dates to be added by the sytem. The
first date would be todays date and the other date should
be todays date + 1.

This run off of one database but is using a few tables.
The strange thing with this is it was working for about a
day. Than I had to make a change to the page (added text)
and when I saved it something changed. I can't get it to
work again.

Thanks!

 

[Kathleen Anderson [MVP - FrontPage]]

John:
Try this for the date logic:

WHERE (d.batchstartdatetime > Date() and d.batchstartdatetime < (Date() +
1))


--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

 

[John]

Hi,
I am getting ...

'Date' is not a recognized function name

I was trying to look at DatePart but I am getting an error
about needing 2 arguments so I must have a syntax problem.

I also tried GetDate. That worked with no errors but the
problem is GetDate also looks for the time of day with is
to exact.

Thanks for your help!

-----Original Message-----
John:
Try this for the date logic:

WHERE (d.batchstartdatetime > Date() and

d.batchstartdatetime < (Date() +

 

[Kathleen Anderson [MVP - FP]]

What kind of database are you using?

 

[John]

Hi,
I am using MS SQL2000 and IIS with Frontpage with
Sharepoint. What I need is some way of getting the system
date without the time. 8/5/2004

I am still looking around and I want to thank you for your
help!!

 

[Thomas A. Rowe]

Try:

<%=FormatDateTime(Date,vbShortDate)%>

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

 

[John]

Hi
I will try this but I didn't know you could run ASP from
within a DB query.

I will let you know how I make out!

Thanks for the help!

 

[Thomas A. Rowe]

Just place the code on the page where ever you want the date to appear, since it is coming from the
server, it has no impact on any db query.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

 

[John]

Hi,
I want to have the date added to the DB query. Not have it
show up on the page. What I am looking to do is to run a
query using today's date as part of it and than show the
results on the screen. I was trying to use GetDate but it
is to exact for what I want.

Thanks!

-----Original Message-----
Just place the code on the page where ever you want the

date to appear, since it is coming from the

 

[Kathleen Anderson [MVP - FrontPage]]

John:
Try this:

WHERE d.batchstartdatetime BETWEEN GETDATE() AND (GETDATE()+1)

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

 

[Kevin Spencer]

GetDate in your SQL is what you need to use. However, I can't tell from your
post what "to exact for what I want" means. What is the problem you're
experiencing with GetDate? I can tell you how to use it correctly if you do.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[John]

Hi,
When I use GetDate it also has the current time.

What I want to do is to run a query at say 3:00PM. This
query should look for everything that happened today. So
the query would be run at say 8/6/2004 3:00 PM but it
should look for everything between 8/6/2004 midnight and
whenever the query was run. The problem I am having with
GetDate is it looks for everything with a time of exactly
when the GetDate was run so I always get nothing back.

Thanks for you help!

 

[John]

Thanks! I will try this.

-----Original Message-----
John:
Try this:

WHERE d.batchstartdatetime BETWEEN GETDATE() AND (GETDATE ()+1)

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/




.

 

[Kevin Spencer]

That won't work if he wants everything from midnight to now. It will fetch
everything from now to tomorrow (nothing).

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Kevin Spencer]

Hi John,

The following will get all records with today's date:

WHERE DATEDIFF(day, d.batchstartdatetime, GETDATE()) = 0

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[John]

Hi,
I plugged this in and it looks good. I have to wait until
Monday when start production to test it but right now I
think its working great! Thank you so much for your
help!!! You made my day!

 

[John]

Hi,
Well I wanted to thank everyone who helped me with this
problem. I got it to work and put it into prduction today.
Everyone is really happy!

Thanks a lot everyone!!