DatePart query

[David]

Hi Group,
i am trying to use the DatePart function on my SQL2000 database. I have a
table called visitors with a field called DateTimeEntrance which is filled
everytime a visitor enters the site. I am trying to build a stat page where
i display the total amount of visitors per day, week, month, year and i
can't seem to get the syntax correct.
I get an error with the following code:

sql = "SELECT * FROM tblVisitors WHERE (DatePart("yyyy",
[datetimeentrance])) = (DatePart("yyyy", date()))"

can anyone help me with the correct syntax?

thanks in advance for any help received

David

 

[Tom B]

sql = "SELECT * FROM tblVisitors WHERE (DatePart("yyyy",
[datetimeentrance])) = (DatePart("yyyy", date()))"

1) You didn't escape your quotes (double them up)
2) DatePart uses a different format than VB, refer to BOL



sql="SELECT [columnX], [columnY] FROM tblVisitors WHERE
DATEPART(yyyy,[datetimeentrance])=DATEPART(yyyy,GETDATE())"

I think that's right

 

[David]

Hi,
thanks Tom for your help. I still don't have it working. You said i didn't
escape my quotes(double them up) but in your example you have none, did i
miss something here?
You said DatePart uses a different format, which is?? and what is BOL?

sorry if the answer is there, i maybe just don't understand your answer.

thanks
David

sql = "SELECT * FROM tblVisitors WHERE (DatePart("yyyy",
[datetimeentrance])) = (DatePart("yyyy", date()))"

1) You didn't escape your quotes (double them up)
2) DatePart uses a different format than VB, refer to BOL



sql="SELECT [columnX], [columnY] FROM tblVisitors WHERE
DATEPART(yyyy,[datetimeentrance])=DATEPART(yyyy,GETDATE())"

I think that's right

Hi Group,
i am trying to use the DatePart function on my SQL2000 database. I have a
table called visitors with a field called DateTimeEntrance which is filled
everytime a visitor enters the site. I am trying to build a stat page where
i display the total amount of visitors per day, week, month, year and i
can't seem to get the syntax correct.
I get an error with the following code:

sql = "SELECT * FROM tblVisitors WHERE (DatePart("yyyy",
[datetimeentrance])) = (DatePart("yyyy", date()))"

can anyone help me with the correct syntax?

thanks in advance for any help received

David

 

[Tom B]

I wasn't very clear...sorry.


My suggestion
would produce the following sql statement being sent to the server

SELECT [columnX], [columnY] FROM tblVisitors WHERE
DATEPART(yyyy,[datetimeentrance])=DATEPART(yyyy,GETDATE())

1)I just mentioned the double quote thing to clear up what your errors would
be. If you want to include a quote in a string you have to escape it. For
example:

Dim aString
aString="Then he said ""hello there"""

would store

The he said "hello there"
in the aString variable.


2) The DatePart format that you used is for vb/vbscript
DatePart("yyyy", date())
but you wanted to use the T-SQL version which is
DatePart(yyyy, GetDate())

3) Books OnLine (BOL) should be installed on your SQL Server. It's also
available for download separately at www.microsoft.com/sql

Hi,
thanks Tom for your help. I still don't have it working. You said i didn't
escape my quotes(double them up) but in your example you have none, did i
miss something here?
You said DatePart uses a different format, which is?? and what is BOL?

sorry if the answer is there, i maybe just don't understand your answer.

thanks
David

sql = "SELECT * FROM tblVisitors WHERE (DatePart("yyyy",
[datetimeentrance])) = (DatePart("yyyy", date()))"

1) You didn't escape your quotes (double them up)
2) DatePart uses a different format than VB, refer to BOL



sql="SELECT [columnX], [columnY] FROM tblVisitors WHERE
DATEPART(yyyy,[datetimeentrance])=DATEPART(yyyy,GETDATE())"

I think that's right

Hi Group,
i am trying to use the DatePart function on my SQL2000 database. I

have

a

table called visitors with a field called DateTimeEntrance which is filled
everytime a visitor enters the site. I am trying to build a stat page where
i display the total amount of visitors per day, week, month, year and i
can't seem to get the syntax correct.
I get an error with the following code:

sql = "SELECT * FROM tblVisitors WHERE (DatePart("yyyy",
[datetimeentrance])) = (DatePart("yyyy", date()))"

can anyone help me with the correct syntax?

thanks in advance for any help received

David