473,558 Members | 3,041 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Select Statement Issue - Date Formating/Selecting

52 New Member
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 2592
folderol
5 New Member

<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
movieking81
52 New Member
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
2141
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 = ....... The problem I am having is that I want the selected records only with a ShipETA less than 31/12/2005.
3
7651
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 "@" at character 1 address=# var = select max(id) from passwd; ERROR: parser: parse error at or near "var" at character 1 address=# :var = select max(id) from passwd; ERROR: parser: parse error at...
4
5113
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 the file name by hand. So I converted the macro to code using tools-->references. The converted macro included the following statement: DoCmd.OutputTo acQuery, "qselLabelsBloodLog_output",...
5
4463
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? Example: I have an SqlDataSource with a ControlParameter <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:XYZ %>"
5
2548
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 Bilag Where Mdates Between #1/1/2006# And #31/1/2006#" But when I try it from my vb.net app, I get ALL the records in the tabel?? What goes wrong? I haven't been able to find any info on the net,...
2
13546
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 averaged values over a years time. What I have is weather data. There is a new record every 5 minutes, every day. So. What I want to do with one SQL statement is figure out the Average of...
1
4198
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 ConcertName AS Name, ConcertDate AS Date, ConcertTime AS Time, ConcertPlace AS Place FROM Concerts UNION SELECT EventName AS Name, EventDate AS Date, EventTime AS Time, EventPlace AS Place FROM Events ORDER...
5
2252
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 occurred that caused thousands of records to be created with a consistent error in a single field. I can identify those easily records with a select statement. I'd *really* rather not have to change...
2
3880
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 selecting a date in this format and I need to select data based on this range. i.e.
0
7629
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7550
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8061
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6184
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5455
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3603
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3579
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1165
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
872
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.