Convert varchar field to datetime value

Last Post 09 Oct 2007 04:31 AM by arif101. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
arif101
New Member
New Member

--
08 Oct 2007 05:12 AM
I have linked a MYSQL DB to my MSSQL DB and am now required to transfer the data held in MYSQL to the new relevant tables in MSSQL, the only problem is that all the fields in MYSQL have been set to varchar. How can I pull the information from MYSQL and convert it to the required format before putting it into the MSSQL tables?

The query I am trying at the moment but doesn't work is:
SELECT
CONVERT(datetime, <DOB>
FROM OPENQUERY
(<server>, 'select <DOB> from <table>')


I am also having problems pulling fields which have NULL or blank values into MSSQL using:
SELECT
*
FROM OPENQUERY
(<server>, 'select * from <table>')

Any help or advise would be appreciated!!
Arif
arif101
New Member
New Member

--
08 Oct 2007 09:01 AM
quote:

what do the dates look like? can u give us a couple of sample records?


The format at the moment is dd/mm/yyyy stored as a varchar though. here are some of the entries:

27/09/1971
29/09/1979
13/04/1981
18/01/1978
13/09/1958
18/01/1978
14/06/1977
14/06/1977
14/12/1979
01/11/1984
07/02/1957
16/06/1942
22/06/1970
20/04/1973
arif101
New Member
New Member

--
09 Oct 2007 04:31 AM
Hi Russell

Thanks for your help on this the query you gave me does convert it to the datetime value in MSSQL.

Do you know anyway I can get the query above to also pull in the MYSQL blank and null values?
You are not authorized to post a reply.

Acceptable Use Policy