472,353 Members | 2,183 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

Select Statement Issue - Date Formating/Selecting

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
2 2517

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

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

Similar topics

2
by: David | last post by:
Hi, I have part of my SQL statement in my asp page as follows: WHERE ((pcbforecast.ShipETA < '31/12/2005') and (products.BBProductName =...
3
by: joseph speigle | last post by:
hello list, I want to do something like the following: address=# @var = select max(id) from passwd; ERROR: parser: parse error at or near "@"...
4
by: Polly | last post by:
I had a macro that ran a parameter query and created and opened an Excel file with the system date as part of the file name, but I had to change...
5
by: Martin Bischoff | last post by:
Hi, is it possible to modify the values of a SqlDataSource's select parameters in the code behind before the select command is executed? ...
5
by: Henning M | last post by:
Hi all, I having some problems with Access and selecting records between dates.. When I try this in access, it works fine!! "Select * from...
2
by: chrisale | last post by:
Hi All, I've been racking my brain trying to figure out some sort of Sub-Select mySQL statement that will create a result with multiple rows of...
1
by: BlackMustard | last post by:
hi all, i am currently using the following union select statement to select records from two of my tables to a gridview in asp.net: SELECT...
5
by: Chris Cowles | last post by:
I use an application that uses Oracle 8.1.7. All functions of the application are completed with calls to stored procedures. A data entry error...
2
by: shannonwhitty | last post by:
I am able to extract dates in the correct format i.e. SELECT CONVERT(VARCHAR(8), GETDATE(), 3) =dd/mm/yy My issue is that my users are...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.