Post reply

Create view problem, 'DateInterval.year' is not a recognized datepart option.

  • February 19, 2008 at 6:42 am

    #183132

    HI guys.

    I´m hoping someone can help me with the small matter I am struggling with at the mo.

    Here is the SQL command I am trying to run

    //Begin SQL//

    CREATE VIEW [dbo].[Syna_allt_ar]

    AS

    SELECT TOP 100 PERCENT own_id, call_id, call_date, call_time

    FROM dbo.callIn

    WHERE (DATEPART(year, call_date) = DATEPART(DateInterval.year, GETDATE()))

    ORDER BY call_date

    GO

    //End SQL//

    And the error message i get is the following

    Msg 155, Level 15, State 1, Procedure Syna_allt_ar, Line 5

    'DateInterval.year' is not a recognized datepart option.

    I am trying to write a script that creates a database and then tables , procedures and views.

    Any help appreciated

  • February 19, 2008 at 7:23 am

    #779703

    select DATEPART(year, GETDATE())

    copy and paste? 🙂

    Piotr

    ...and your only reply is slàinte mhath

  • February 19, 2008 at 7:39 am

    #779713

    I´m not getting it?

    I scripted the view as Create to basically , are you saying there is one to many of these? )

  • February 19, 2008 at 7:45 am

    #779718

    jon (2/19/2008)

    I´m not getting it?

    I scripted the view as Create to basically , are you saying there is one to many of these? )

    Hello Jon,

    This is what he means

    CREATE VIEW [dbo].[Syna_allt_ar]

    AS

    SELECT TOP 100 PERCENT own_id, call_id, call_date, call_time

    FROM dbo.callIn

    WHERE (DATEPART(year, call_date) = DATEPART(year, GETDATE()))

    ORDER BY call_date

    Copy this statement and execute.

    Thanks


    Lucky

  • February 19, 2008 at 7:57 am

    #779730

    Ahh silly me, lack of experience there, thanks both of you.

    😀

  • February 19, 2008 at 9:44 am

    #779792

    Heh sorry, I was unclear 🙂

    I also asked about clipboard as this kind of errors often occurs when you copy and paste code 🙂

    Piotr.

    ...and your only reply is slàinte mhath

  • February 19, 2008 at 10:02 am

    #779806

    This is a better way to do the WHERE clause to select data for the current year because:

    1. It does not have to apply a function to each row of the table.

    2. It can use an index on call_date, if it exists.

    where

    -- Call Date greater than or equal first day of this year

    call_date >= dateadd(year,datediff(year,0,getdate()),0) and

    -- Call Date less than first day of next year

    call_date < dateadd(year,datediff(year,0,getdate())+1,0)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply