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

• jon-645606

  SSC Veteran

  Points: 242

  More actions

  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

• Piotr.Rodak

  SSCrazy Eights

  Points: 9376

  More actions

  February 19, 2008 at 7:23 am

  #779703

  select DATEPART(year, GETDATE())

  copy and paste? 🙂

  Piotr

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

• jon-645606

  SSC Veteran

  Points: 242

  More actions

  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? )

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  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

• jon-645606

  SSC Veteran

  Points: 242

  More actions

  February 19, 2008 at 7:57 am

  #779730

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

  😀

• Piotr.Rodak

  SSCrazy Eights

  Points: 9376

  More actions

  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

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  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)