Dates - # delimiters

[Cullen411]

i came across this code

<%
Dim FifteenAgo
Dim FifteenFromNow
fifteenAgo=DateAdd("n",-15,Now())
fifteenFromNow=DateAdd("n",15,Now())
SQL="SELECT * FROM table WHERE fieldValue>= fifteenAgo AND fieldValue<=fifteenFromNow"

Should there be the # delimiters in this code

 

[DotNetGnat]

yes there should be # delimiter because your date functions return date type variables...

-DNG

 

[Cullen411]

What about this
SELECT * FROM TABLE WHERE NOW() - tblLogins.Date<30

Should there be delimiters around the Now() function? and why?

thanks for your help

 

[Sheco]

SELECT * FROM TABLE WHERE NOW() - tblLogins.Date<30

As written, the Now() is not a function call... it is just the word "Now" followed by a couple of parenthesis.

So you'd need something more along the lines of:
sSQL = "SELECT * FROM TABLE WHERE " & cStr(NOW() - tblLogins.Date) & " < 30"

Except that is bad also because you should use the DateDiff() function instead of raw math on date variables because they are in milliseconds and i think it is safe to assume you are not looking for dates 30 milliseconds apart... probably more like minutes or days would be my guess.

 

[johnwm]

As far as I know date variables are stored as Doubles, with the integer part representing whole days since 30 Dec 1899.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Sheco]

I thought it was milliseconds since Jan 1, 1970 but maybe I was thinking of C Language dates.

 

[damber]

As no-one else has mentioned it, you usually use # characters in MS Access, but not in SQL Server or Oracle etc, so it depends on which database you are using. However in your initial example, the code uses vars declared in the ASP page inside the SQL string built for execution in the database - which will not have a clue what "fifteenAgo" is, instead the vars should be formatted and concatenated with the string using appropriate delimiters (' or #).





A smile is worth a thousand kind words. So smile, it's easy!

 

[Cullen411]

Sheco, great point that makes sense to use DateDiff.

I'm not sure what you mean by Now() not being a function call? and why I would convert it to

sSQL = "SELECT * FROM TABLE WHERE " & cStr(NOW() - tblLogins.Date) & " < 30

 

[Tarwn]

Ok, I'm going to correct a few of these posts because there are a large number of errors:

1) You only need # or single quotes around date values in SQL statements, so 1/1/2001 would require #'s or singlequote's around it, but a function that returns a date (like getdate() in SQL Server) would not require them
2) In the original post, the sample code has another error, in that it does not concatenate the values of the variables into the string:

SQL="SELECT * FROM table WHERE fieldValue>= [highlight]" & fifteenAgo & "[/highlight] AND fieldValue<=[highlight]" & fifteenFromNow

According to the previous statement, we now need to add #'s or single-quotes like so:

SQL="SELECT * FROM table WHERE fieldValue>= [highlight]#[/highlight]" & fifteenAgo & "[highlight]#[/highlight] AND fieldValue<=[highlight]#[/highlight]" & fifteenFromNow
[highlight] & "#"[/highlight]

3) I am assuming tblLogins is a table in your database and not a local object in your ASP code, in that case Sheco's code will not work. Your original example may, if Access supports the Now() function (which I believe it does). If you did want to compare the difference between the result of Now() in the ASP code and the data in your table, you would need #/'s because you would be concatenating the return value of Now() into your SQL string.

[b]Your Original Comparison[/b]
SELECT * FROM TABLE WHERE NOW() - tblLogins.Date<30
[i]This should work as long as you remember that the NOW() function is being called inside access. [/i]

[b]Sheco's[/b]
sSQL = "SELECT * FROM TABLE WHERE " & cStr(NOW() - tblLogins.Date) & " < 30"
[i]This will not work because you don't have access to a tblLogins object in your ASP code[/i]

[b]Using Now() From ASP in your SQL string[/b]
sSQL = "SELECT * FROM TABLE WHERE #" & NOW() & "# - tblLogins.Date < 30"
or
sSQL = "SELECT * FROM TABLE WHERE DateDiff('s',#" & NOW() & "# - tblLogins.Date) < 30"

I just noticed that damber mentioned some of these problems, hopefully the additional information I provided will be useful.

Basically think of it this way. You are building a string to send to MS Access (or whatever database). The syntax of a SQL statement is designed so that the database can easily determine the difference between differant arguments. For example: if you entered a date without anything around it, ie 1/1/2000, then how does the database know whether you are sending it a date or asking it to compute 1 divided by 1 divided by 2000? While it would be possible to make a funciton to get the right one most of the time, it is much more efficient and less prone to error for the system to simply require symbols surrounding the value in certain circumstances.
So raw string/text/character data requires single quotes around it. Raw dates require #'s or single-quotes around them.

One thing that often helps is to print the SQL string out to the screen. That way you will be able to see exactly what your going to send to the database. You can also paste this into Access or Query Analyzer to see how the database will handle it.

In any case, I've gone on long enough, hope some of this helps,
-T

 

[Sheco]

The code that I posted wasn't supposed to work... it was the intermediate step of fixing the string delimmiter problem that put the Now() function inside the string so it couldnt be called.

So the code was supposed to fix that problem only, not to be a final soltion. That is why it was immediately followed by "Except that is bad also [..]"

I guess I should have put a new bit of code at the end that both fixed the string concat and the WHERE clause.

 

[Tarwn]

Sorry, I only pointed it out because it was misleading to move the table name into the ASP code.

 

[Sheco]

Oh don't be sorry... it is good that you pointed it out, I agree it was misleading because I didnt put followup code for the DateDiff.

 

[Tarwn]

No, I refuse to not be sorry. Accept my apology or else I will..err...yeah, that. So there...