Date And Now !

[Adam Knight]

I have stored a date value using the now function.
This stores dates like this: 5/3/2004 3:59:23 PM

I am wanting to run a query that compares todays date with the date in the
db field.
I imagine this means i have to isolate only the date section of the input
saved by using 'NOW()'
Using jetsql which would be the best way to accomplish this.

Thanks,
Adam.

[dlbjr]

Place this text in the field item of a query:

SDATE: DateSerial(Year([STAMP]),Month([STAMP]),Day([STAMP]))

Replace the word STAMP with the exact name of your date field name.

'from dlbjr

'Unambit from meager knowledge of inane others,engender uncharted sagacity.

[Adam Knight]

This is what i have come up with so far:

select invoice_member from invoice where
DateSerial(Year("invoice_date"),Month("invoice_dat e"),Day("invoice_date")) =
DateAdd("yyyy",-1,Date)

When i try to execute this i am getting an error; 'this expression is typed
incorrectly or to complex'. Can anyone see a problem with this code.

AK

[Aaron Bertrand [MVP]]

If you have an index on the datecolumn, then the most efficient query would
be
WHERE datecolumn >= Date() AND datecolumn < dateadd("d", 1, Date())
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Adam Knight" <ad**********@optusnet.com.au> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...

I have stored a date value using the now function.
This stores dates like this: 5/3/2004 3:59:23 PM

I am wanting to run a query that compares todays date with the date in the
db field.
I imagine this means i have to isolate only the date section of the input
saved by using 'NOW()'
Using jetsql which would be the best way to accomplish this.

Thanks,
Adam.

[Bob Barrows [MVP]]

Adam Knight wrote:

I have stored a date value using the now function.
This stores dates like this: 5/3/2004 3:59:23 PM

I am wanting to run a query that compares todays date with the date
in the db field.
I imagine this means i have to isolate only the date section of the
input saved by using 'NOW()'
No!
Using jetsql which would be the best way to accomplish this.

Select ... WHERE datefield >= Date() AND datefield < Date()
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote:

Adam Knight wrote:

I have stored a date value using the now function.
This stores dates like this: 5/3/2004 3:59:23 PM

I am wanting to run a query that compares todays date with the date
in the db field.
I imagine this means i have to isolate only the date section of the
input saved by using 'NOW()'

No!

Using jetsql which would be the best way to accomplish this.

Select ... WHERE datefield >= Date() AND datefield < Date()

Oops! I forgot the DateAdd(). See Aaron's post.

Bob
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[PW]

In this example, I am searching for records that fall between two dates ...

mySQL = "Select * from myTable where some_column = some_value "
if Request.QueryString("txtDateFrom") <> "" then
myDateFrom = CDate(Request.QueryString("txtDateFrom"))
myDD = Day(myDateFrom)
myMM = right("00" & Month(myDateFrom),2)
myYYYY = Year(myDateFrom)
myISOfr = myYYYY & "-" & myMM & "-" & myDD
mySQL = mySQL & "and CommDate >= #" & myISOfr & "# "
End if
if Request.QueryString("txtDateTo") <> "" then
myDateTo = CDate(Request.QueryString("txtDateTo"))
myDD = Day(myDateTo)
myMM = right("00" & Month(myDateTo),2)
myYYYY = Year(myDateTo)
myISOto = myYYYY & "-" & myMM & "-" & myDD
mySQL = mySQL & "and CommDate <= #" & myISOto & "# "
End if