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

Subscribe Post Reply

2577

folderol

<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

 resultssql = "SELECT * FROM testtable 

where name = '" & request("name") & "' AND 

cast(fromd as datetime) >= '" & getdatefrom & "' AND 

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

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

Similar topics

Dates in ASP SQL Select Statement....

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

ASP / Active Server Pages

selecting into a variable like @var=select ...

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

PostgreSQL Database

Run-time error '3011' in select statement in access2002

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

Microsoft Access / VBA

Modify SqlDataSource parameters before select

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

ASP.NET

Access Databse "Select * from Bilag Where Mdates Between #1/1/2006# And #31/1/2006#"

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

Visual Basic .NET

SELECT multiple rows with average of a range of values

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

MySQL Database

insert column in union select statement

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

Microsoft SQL Server

[newbie] select records, update with procedure

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

Oracle Database

Select date in british format

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

Microsoft SQL Server

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

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

Online Marketing