Convert 6 digit numerical field to short date 1

[fishfartus]

I am importing a text file with a fixed width field 6 characters long. I want to convert this six digit numerical or text field into a date field, but everything I try displays an odd date. The six digits are imported something like "010905", and I want them converted to 01/09/2005, with date properties. Any suggestions.

 

[r937]

i think you will first have to import them into a text field, then use substrings to construct and update a date field

whenever i do any file importing, i always import the file into a "landing table" which has these text fields, then insert into the final target table using whatever conversion expressions are necessary

e.g.

insert 
  into finaltable 
     ( ... , datefield, ... )
select ...
     , '20'&mid(textfield,5,2)
       &'-'&mid(textfield,1,2)
       &'-'&mid(textfield,3,2)
     , ...
  from landingtable

make sure you specify mm after the year, not dd



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[PHV]

Take a look at the DateSerial, Left, Mid and Right functions:
SELECT DateSerial(Right([text field],2),Mid([text field],3,2),Left([text field],2)) As myDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

PHV, you 'n me have our DDMM's and MMDD's opposite

for example, i see '020304' as 2004 feb 04, whereas you see it as 2004 mar 02

so, fishfartus (whoa, what a handle), make sure you know which is which

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[PHV]

Good point Rudy.

 

[fishfartus]

Thanks for the help PHV. I was able to use the DateSerial function in an update query, then go to the updated table and change the data properties to "Date" without loosing my data. I have spent 6 hours trying to figure this out, and you helped take care of it in 3 minutes. Way-to-go!!