SQL guru question

["Norbut, Jim" <Jim.Norbut@xxxxxxxxxxxxxxx>]

I have a file called headcount which gets data pumped to each month on
the 5th of the month.

 

FILEDATE                      EMPLOYEEID
EMPLOYEESTATUS                        LASTNAME

Jun  5 2008 11:36PM      110886
Active                                              Wong

 

Filedate is a  CHAR(26) Field.

EmployeeID is a Char(41) Field

EmployeeStatus is a CHAR(50) Field

LastName is a CHAR(30) Field

 

I also have a file called HCARCHIVE

With the same exact definition which has all the data from previous
months in it.

(on the 5th of the month...that Headcount file gets overwritten with a
new version)

 

 

What I am looking to do is automate a daily process that says something
like

 

Insert into HCARCHIVE 

    Only the records from headcount if max(filedate) in headcount >
max(filedate) in hcarchive.

 

So that way the hcarchive file gets updated with the latest data
automatically w/o me having

To remember to do it.

 

Hoping there is some sort of conversion to take a text like that

And convert it into a date that SQL will like.

 

I've been dabbling with CASE statements...and views...but not making
much headway.