Assistance please using txt

I have received a table of data that has a field containing date
information. Unfortunately it was derived from a MainFrame dump and
originated as a txt file and was then ported into an Access MDB file
before it became an SQL table. The date format is vchar(50) and
actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
need to run a select statement for a range of dates such as 010104
thru 030104. Unfortunately being a charecter field this returns
incorrect results under a majority of cases. Back in my dBase days
there was a VAL() that could be used in this case but I have been
unable to find anything comperable in SQL. Can anyone help me please?

Thanks in advance

Steve"Steve" wrote:
> I have received a table of data that has a field containing date
> information. Unfortunately it was derived from a MainFrame dump and
> originated as a txt file and was then ported into an Access MDB file
> before it became an SQL table. The date format is vchar(50) and
> actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
> need to run a select statement for a range of dates such as 010104
> thru 030104. Unfortunately being a charecter field this returns
> incorrect results under a majority of cases. Back in my dBase days
> there was a VAL() that could be used in this case but I have been
> unable to find anything comperable in SQL. Can anyone help me please?
> Thanks in advance
> Steve

Steve,

I've never worked with dBase, but I assume that VAL() would convert to an
integral data type, so that wouldn't work either: 123103 would be greater
than 010104. Also, if you have any dates before Y2K, you'll have issues
there.

One more issue: you mentioned big iron... if the date field in the mainframe
file was PIC 9(6), you might want to verify that somewhere between the file,
Access, and SQL Server, you still have all leading and trailing zeroes...

select min(len(somefld)), max(len(somefld))
from sometable

...Anyhoo, I would recommend using string functions (like LEFT, RIGHT, and
SUBSTRING) and the CONVERT function to create a real datetime column.
Barring that, you could convert the data to a datetime for the query.

Assuming that every field is 6 characters in the format mmddyy, here's a
little test I cobbled together. Note that I'm assuming you're using SQL
Server 2K: otherwise the table variable won't work. Also, I chose what SQL
Server BOL calls the ANSI date format, but YMMV depending on your regional
date/time settings...

set nocount on

declare @.test table (
src varchar(50),
dest datetime
)

insert @.test values ('123198', null)
insert @.test values ('010199', null)
insert @.test values ('123103', null)
insert @.test values ('010104', null)

--Use 2: the ANSI (yy.mm.dd) style for conversion (the
--final parameter in the convert call)
update @.test
set dest = convert(
datetime,
right(src, 2) + '.' +
left(src, 2) + '.' +
substring(src, 3, 2),
2
)

--Out of whack
select * from @.test order by src

--In whack
select * from @.test order by dest

Craig|||Steve,
Datetime fields (there are no Date fields) in SQL Server need to be in
the format yyyymmdd, so you need to format the field first before inserting
it into your database. Do that like this:

declare @.myDate as varchar(50)

set @.myDate = '010104'

select cast(
'20' +
right(@.myDate,2) +
substring(@.myDate,3,2) +
left(@.myDate,2) as datetime)

I hard-coded '20' as the century.
Also, the way to import the file would probably to create a DTS package and
load the data into a temp table, then create a procedure using the above
statement as part of the stored procedure.
OK, now for the pitch. If you are new to SQL Server 2000, a great way to
get up to speed in just a few hours is with our video series on SQL Server
2000 at www.TechnicalVideos.net. Our videos give tips and tricks from
experts in the field, while they show you on the screen just how to do them.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"Steve" <shull@.dpd.dallascityhall.com> wrote in message
news:6318603c.0403041803.7a69d4f4@.posting.google.c om...
> I have received a table of data that has a field containing date
> information. Unfortunately it was derived from a MainFrame dump and
> originated as a txt file and was then ported into an Access MDB file
> before it became an SQL table. The date format is vchar(50) and
> actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
> need to run a select statement for a range of dates such as 010104
> thru 030104. Unfortunately being a charecter field this returns
> incorrect results under a majority of cases. Back in my dBase days
> there was a VAL() that could be used in this case but I have been
> unable to find anything comperable in SQL. Can anyone help me please?
> Thanks in advance
> Steve