DatePart query
 

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

Re: DatePart query
 

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" <db010a6370@blueyonder.co.uk> wrote in message
news:uKB5X5DfDHA.2984@TK2MSFTNGP11.phx.gbl...
> 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
>
>

Re: DatePart query
 

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

"Tom B" <shuckle@hotmail.com> wrote in message
news:Oz8%23nLFfDHA.2348@TK2MSFTNGP12.phx.gbl...
> 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" <db010a6370@blueyonder.co.uk> wrote in message
> news:uKB5X5DfDHA.2984@TK2MSFTNGP11.phx.gbl...
> > 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
> >
> >
>
>

Re: DatePart query
 

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




"David" <db010a6370@blueyonder.co.uk> wrote in message
news:O3GK6rJfDHA.1872@TK2MSFTNGP09.phx.gbl...
> 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
>
> "Tom B" <shuckle@hotmail.com> wrote in message
> news:Oz8%23nLFfDHA.2348@TK2MSFTNGP12.phx.gbl...
> > 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" <db010a6370@blueyonder.co.uk> wrote in message
> > news:uKB5X5DfDHA.2984@TK2MSFTNGP11.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>
>