You need to use (DATEPART(m,work_day) =#m#) If you have access to the ENTERPRISE MANAGER in SQL SERVER, you should have access to the online books, which have some pretty good examples in them. Not sure if it needs to be installed (option) when you install the Enterprise Manager.
Here is the document for datepart from the online books: DATEPART (T-SQL) Returns an integer representing the specified datepart of the specified date. Syntax DATEPART(datepart, date) Arguments datepart Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft� SQL Server(tm). Datepart Abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw hour hh minute mi, n second ss, s millisecond ms The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year. The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week. date Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0. If you specify only the last two digits of the year, values that are less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values that are greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years. For information about specifying time values, see Time Formats. For information about specifying dates, see datetime and smalldatetime. Return Types int -----Original Message----- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 3:10 PM To: CF-Talk Subject: SQL help Hi all We are finally tring to upgrate from Access to SQL 2000. I am working on making the necessary changes to the queries. One I am having a problem with is were the WHERE statement is: WHERE (proj_name = '#Qgetinfo.proj_name# ') and(datepart('M',work_day) =#m#) m=number for the month: m=5. work_day is the field in the db table. this works fine with Access(data type date/time), but with SQL I get the error: Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid parameter 1 specified for datepart when the data type is smalldatetime and if I change it to timestamp, no error but no records are returned either. This is used to create a report based on the project selected and the month. THanks Rodney ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
