none
Year function in sql query

    Question

  • I'm trying to add a field in query design SQL EXPRESS as follow

    SELECT Year([Created_Date]) as exp1

    FROM Tablex

    or

    SELECT Datepart(year,[Created_Date]) as Years

    FROM Tablex

    Table comming from MSAccess

    I have donne in MSAccess many time without a problem , but I get an error in SQL

    what wrong , please help


    Wednesday, December 21, 2005 12:04 AM

All replies

  • What's the error you're getting?
    Wednesday, December 21, 2005 12:31 AM
  • Error source:Microsoft Jet Database Engine

    Error Message:Undefined function '[Year]' in expression

    please note it adds [ ] Brakets to year

    Wednesday, December 21, 2005 1:01 AM
  • Than that's the problem: the adding of brackets to "Year" makes it a column identifier instead of a function name.

    Are you passing that SQL statement to Access via a OleDbCommand object? And if so, can you please paste that code here?

    Wednesday, December 21, 2005 1:05 AM
  • The sql statement is as I stated earlier

    Year([Created_Date]) in the query grid

     

    Wednesday, December 21, 2005 1:10 AM
  • But you said, "please note it adds [ ] Brakets to year"

    That means it looks like this: [Year]([Created_Date])

    Or like this: [Year([Created_Date])]

    Correct? Or does it add the brackets ONLY to the error message and NOT to the query itself?

    Wednesday, December 21, 2005 1:18 AM
  • That means it looks like this: [Year]([Created_Date]

    It adds it to query grid and error message as well.

    Wednesday, December 21, 2005 1:21 AM
  • Than that's the problem: the adding of brackets to "Year" makes it a column identifier instead of a function name.
    Wednesday, December 21, 2005 1:26 AM
  • So the question is why does it added and cannot evaluate

    Year([Created_Date])

    I just try it on the same table in access and it works well

    Wednesday, December 21, 2005 1:30 AM
  • Are you passing that SQL statement to Access via a OleDbCommand object? And if so, can you please paste that code here?
    Wednesday, December 21, 2005 3:37 PM
  • I have the same problem. Did you find a solution?
    To work around this bug I'm going to make the query in the access mdb file that calculates and lists in columbs the Year, Month and Day. Then I'll set the SQLDataSource to use that view of the data instead of the table directly. However, there should be a way to use functions inside of the query designer build into the express.
    Friday, December 23, 2005 8:38 PM