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_date"),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())

 

[Bob Barrows [MVP]]

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

 

[Bob Barrows [MVP]]

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

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

Bob

 

[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