DatePart query

Discussion in 'ASP General' started by David, Sep 16, 2003.

  1. David

    David Guest

    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
    David, Sep 16, 2003
    #1
    1. Advertising

  2. David

    Tom B Guest

    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" <> wrote in message
    news:...
    > 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, Sep 16, 2003
    #2
    1. Advertising

  3. David

    David Guest

    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" <> wrote in message
    news:Oz8%...
    > 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" <> wrote in message
    > news:...
    > > 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
    > >
    > >

    >
    >
    David, Sep 16, 2003
    #3
  4. David

    Tom B Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:Oz8%...
    > > 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" <> wrote in message
    > > news:...
    > > > 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, Sep 17, 2003
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?QWFyYW43Ng==?=

    datepart function

    =?Utf-8?B?QWFyYW43Ng==?=, Nov 3, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    2,776
    Bruce Barker
    Nov 3, 2005
  2. Learner
    Replies:
    1
    Views:
    984
    Marina Levit [MVP]
    Jan 30, 2006
  3. Tipple
    Replies:
    2
    Views:
    125
    Tipple
    Oct 16, 2003
  4. Chumley the Walrus
    Replies:
    3
    Views:
    140
    Chumley the Walrus
    Aug 27, 2004
  5. le0

    DatePart

    le0, Aug 11, 2006, in forum: ASP General
    Replies:
    1
    Views:
    117
Loading...

Share This Page