By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,978 Members | 1,389 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,978 IT Pros & Developers. It's quick & easy.

Select Statement Issue - Date Formating/Selecting

P: 52
If someone could help me with this, that would be great. I need to select a number of records from an SQL table based on a date range, so I started with this select.

<html>
<code>
resultssql = "SELECT * FROM testtable where name = '" & request("name") & "' AND fromd >= '" & getdatefrom & "' AND fromd <= '" & getdateto & "'"
</code>
</html>

This select does find records, however they are not the correct ones. The records it finds only contain the exact text not the range in which I asked it to search. For instance, it only finds all the January records if I ask it to search between January and March. I assume it has something to do with how the dates are formatted, right? The column in the table is set as a varchar field with the actual dates resembling "January 1, 2007". So I try wrapping the "getdate" variables in something like a formatdatetime() function, but that only works for the variables. If I try wrapping it around the column name it throws an error at me. Can anyone help with the statement or maybe provide an alternative select that works better. Is there any way to format the column text in the table as your selecting it?

Thank you in advance
Feb 20 '07 #1
Share this Question
Share on Google+
2 Replies


P: 5

<html>
<code>
resultssql = "SELECT * FROM testtable where name = '" & request("name") & "' AND fromd >= '" & getdatefrom & "' AND fromd <= '" & getdateto & "'"
</code>
</html>

The column in the table is set as a varchar field with the actual dates resembling "January 1, 2007". So I try wrapping the "getdate" variables in something like a formatdatetime() function, but that only works for the variables. If I try wrapping it around the column name it throws an error at me. Can anyone help with the statement or maybe provide an alternative select that works better. Is there any way to format the column text in the table as your selecting it?

Thank you in advance

Expand|Select|Wrap|Line Numbers
  1. resultssql = "SELECT * FROM testtable 
  2. where name = '" & request("name") & "' AND 
  3. cast(fromd as datetime) >= '" & getdatefrom & "' AND 
  4. cast(fromd as datetime) <= '" & getdateto & "'"

getdatefrom and getdateto can be varchar, but it would be nice if they were in the form '20070101' or '1/1/2007' so the implicit conversion is not tricky.

cast() will convert a string that looks like "January 1, 2007" to 2007-01-01 00:00:00 which will allow you to evaluate >= and <= correctly.

Tom
Feb 20 '07 #2

P: 52
Works like a champ. Thanks. I thought I knew most of the standard functions for vbscript, or at least where to reference them. But, I've never seen cast() before. Also, I would love to change to date format in the table, but I'm not allowed, someone else's call.

Thank again

Dean
Feb 21 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.