Converting datetime from character string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting datetime from character string

Author

Topic

Suzi
Starting Member

4 Posts

Posted - 2007-08-14 : 11:43:01

I am trying to select all members from a SQL db who have a renewal date btw to dates inputted into two text fields, but I am now getting the error above. This is where the problem is coming in...

If strStartDate <> "" and strEndDate <> "" Then
If bParam = True Then
StrSQL = strSQL & " AND "
Else
StrSQL = strSQL & " WHERE deleted=0 AND "
End If
strSQL = strSQL & "renewal_due BETWEEN '%" & Replace(strStartDate,"'","''") & "%' AND '%" & Replace(strEndDate,"'","''") & "%' "
End If

Any help would be greatly appreciated!

Thank you...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-14 : 12:17:02

Suzi,

You should try posting this in a more appropriate forum. This looks like VB. Also, we'd need to know what strSQL equals so we can tell you if the SQL is correct.

Jim

Suzi
Starting Member

4 Posts

Posted - 2007-08-14 : 12:41:29

Hi Jim

Thank you so much for your response, this is the first time I am using something like this. Sorry I should have said I am using ASP...

I am not sure I understand what you mean by what it equals, this is where it is used:
strSQL = "SELECT * FROM member_account_temp"
...

strSQL = strSQL & strOrderBy & " " & strOrder
Set rsUsers = conPSA.Execute(strSQL)

I have added the code in my first post into a search fx that I had working perfectly, you could input a first name or surname or email or more than one and it would find all the relevant members, I have now been asked to include a fx by which they can view members whos renewal date fits between what ever date is added into the text boxes, I am pretty sure the rest of it is correct, I just dont know how to get my datetime renewal_due and the startdate and enddate into a character string...?

I am sorry if I am asking a stupid question, but I am a bit clueless...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-14 : 12:52:56

If bParam = True Then
StrSQL = strSQL & " AND "
if bParam is true the strSQL = AND

Else
StrSQL = strSQL & " WHERE deleted=0 AND "
End If
strSQL = strSQL & "renewal_due BETWEEN '%" & Replace(strStartDate,"'","''") & "%' AND '%" & Replace(strEndDate,"'","''") & "%' "

at this point strSQL either = and renewal_due BETWEEN '%strStartDate%' and '%strEndDate%'
OR
WHERE deleted = 0 AND renewal_due BETWEEN '%strStartDate%' and '%strEndDate%'
End If

Neither of these works in SQL. Take the %'s out from the dates. '07/01/2007' is good '%07/01/2007%' is bad.
'%' is a wildcard in T-SQL. E.g, Where name like 'Su%' returns Suzi, Suzanne, SuperMan etc.

Jim

Suzi
Starting Member

4 Posts

Posted - 2007-08-14 : 13:00:05

Thank you sooo much!!! IT works, just like that!!! :)

Have a great day!! Thanks again!!

:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-16 : 02:00:59

Also, use stored procedure with input parameters and dont use concatenated sql

Madhivanan

Failing to plan is Planning to fail

Suzi
Starting Member

4 Posts

Posted - 2007-08-16 : 04:23:04

Huh?