I have a feeling I'm missing something obvious here, but being new to
SQL Server is my only excuse!

I have a table that holds a variety of data. I am writing a user
report where the user wants to be able to return all data that falls
between a start and end date that they enter.

The query should therefore be along the lines of

SELECT [some columns]
FROM [my_table]
WHERE [column holding date] BETWEEN [start date] AND [end date];

However, the column holding the date information has been defined, for
reasons best known to the designer, as a VARCHAR(10) and the data is
in the format dd/mm/yy.

I have tried CAST, which simply gave an error. I then tried CONVERT
in the form CONVERT(datetime, [column holding date], 3) and the query
runs without error but returns no data, even though I can see there is
data that would meet the criteria in the table.

Can anyone help me with a solution to this?

Thanks

H

On 14 Jun 2004 02:39:58 -0700, Hilary Band wrote:

>I have a feeling I'm missing something obvious here, but being new to
>SQL Server is my only excuse!
>
>I have a table that holds a variety of data. I am writing a user
>report where the user wants to be able to return all data that falls
>between a start and end date that they enter.
>
>The query should therefore be along the lines of
>
> SELECT [some columns]
> FROM [my_table]
> WHERE [column holding date] BETWEEN [start date] AND [end date];
>
>However, the column holding the date information has been defined, for
>reasons best known to the designer, as a VARCHAR(10) and the data is
>in the format dd/mm/yy.
>
>I have tried CAST, which simply gave an error. I then tried CONVERT
>in the form CONVERT(datetime, [column holding date], 3) and the query
>runs without error but returns no data, even though I can see there is
>data that would meet the criteria in the table.
>
>Can anyone help me with a solution to this?
>
>Thanks
>
>H

Hi Hilary,

I (and others in this group) probably can, but need more information.
Please provide a repro script consisting of
* DDL (create table statements, including constraints and indexes)
* sample data (in the form of insert statements)
* the actual query you used.

That gives us the ability to copy and paste into QA, execute it against
our test database and diagnose what's wrong.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

hband@csc.com (Hilary Band) wrote in message news:<5b489863.0406140139.10ee9cc@posting.google.c om>...
> I have a feeling I'm missing something obvious here, but being new to
> SQL Server is my only excuse!
>
> I have a table that holds a variety of data. I am writing a user
> report where the user wants to be able to return all data that falls
> between a start and end date that they enter.
>
> The query should therefore be along the lines of
>
> SELECT [some columns]
> FROM [my_table]
> WHERE [column holding date] BETWEEN [start date] AND [end date];
>
> However, the column holding the date information has been defined, for
> reasons best known to the designer, as a VARCHAR(10) and the data is
> in the format dd/mm/yy.
>
> I have tried CAST, which simply gave an error. I then tried CONVERT
> in the form CONVERT(datetime, [column holding date], 3) and the query
> runs without error but returns no data, even though I can see there is
> data that would meet the criteria in the table.
>
> Can anyone help me with a solution to this?
>
> Thanks
>
> H

While you could certainly work around this in a query, you should
really fix the root problem and change the data type to datetime. This
will avoid awkward conversions, and make sure that MSSQL can use
indexes on the table efficiently. It would also make troubleshooting
your current problem easier, since there is plenty of information
available about working with datetime columns, but rather less about
dates stored as strings. I appreciate that this decision may be out of
your hands, however.

Simon

Thanks for the offers of help. In the meantime I have managed to solve
the problem, which was simply one of getting the syntax right.
Unfortunately changing the column type wasn't an option as the database
was developed by a third party.

Thanks again

H


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Hilary Band (hband@csc.com) writes:
> Thanks for the offers of help. In the meantime I have managed to solve
> the problem, which was simply one of getting the syntax right.
> Unfortunately changing the column type wasn't an option as the database
> was developed by a third party.

Actually, I would consider to add a computed column to the table
where you convert the source column to datetime. You can add an index
on the computed column, provided that you have the following settings
on: SET QUOTED_IDENTIFIER, SET ANSI_WARNINGS, SET ARITHABORT, SET
ANSI_PADDING, SET CONCAC_NULL_YIELDS_NULL, SET ANSI_NULLS. If you run
from Query Analyzer, all these are on. It may be a different from an
application, though. (The settings must also be active for the index
to be used.)

Here is an example:

CREATE TABLE x (x char(10) NOT NULL)
go
ALTER TABLE x ADD y AS convert(datetime, x, 103)
go
CREATE INDEX z ON x(y)
go
insert x (x) VALUES ('15/06/2004')
go
SELECT * FROM x
go
drop table x


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp