What is the data type for [DATE OF BIRTH] field? Try this use cast or convert functions to convert [DATE OF BIRTH] field.
-----Original Message----- From: Diane Schips [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2003 9:49 AM To: [EMAIL PROTECTED] Subject: [wdvltalk] date problems in conversion from Access to SQL Hi all: I have an emergency conversion from an Access database to SQL. I use two functions in the code that I'm having problems getting working in SQL: DateDiff and DatePart. I want to only access data where the difference between the current date and a date in the BirthDate field is greater or less than some number (the person is say, between 25 and 50 - the user selects the ages of the people whose listings they want to see). I also need to be able to select only people who have a specific astrological sign as determined by the birthdate. I had been using datepart to select by month and day. The functions as I had them (these work in Access, not SQL) are as follows: strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME] FROM members WHERE (DateDiff('yyyy', DateValue(DATE OF BIRTH), now >= " & strLowerAge & ")" I tried changing this to: strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME] FROM members WHERE (DateDiff(" & CHR(34) & "yyyy" & CHR(34) & ", DateValue(DATE OF BIRTH), " & date & ") >= " & strLowerAge & ")", but to no avail. I get the error: Microsoft OLE DB Provider for SQL Server error '80040e14' Incorrect syntax near the keyword 'OF'. OF isn't in the code anywhere. Similarly, I have this code (also working in Access but not SQL): strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME] FROM members WHERE (DATEPART('m',[DATE OF BIRTH]) = 4 AND DATEPART('d',[DATE OF BIRTH]) > 19 ) OR (DATEPART('m',[DATE OF BIRTH]) = 5 AND DATEPART('d',[DATE OF BIRTH]) < 21 )" I get the error: Microsoft OLE DB Provider for SQL Server error '80040e14' Invalid parameter 1 specified for datepart. I tried changing it to: strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME] FROM members WHERE (DATEPART(mm, [DATE OF BIRTH]) = 3 AND DATEPART(dd, [DATE OF BIRTH]) > 20 ) OR (DATEPART(mm, [DATE OF BIRTH]) = 4 AND DATEPART(dd, [DATE OF BIRTH]) < 20 )" and get the error: Microsoft OLE DB Provider for SQL Server error '80040e07' The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Help! How do I get them to work in SQL? Diane ____ * The WDVL Discussion List from WDVL.COM * ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to %%email.unsub%% ____ • The WDVL Discussion List from WDVL.COM • ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
