"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
