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 PROTECTED]
