mmddyyyy

[AustinV]

A vendor is giving me date information in the mmddyyyy format. For example, 11291926 is November 29, 1926. I'm trying to convert it to datetime, but I can't find a code here that works:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

another vendor gives me date information in the yyyymmdd format and I use 112 just fine, but this format is throwing me off. Any suggestions?

[Old Pedant]

You're just going to have to pre-convert the data.

Or you could do it with a custom INSERT statement, of course. But there's no good way to bulk insert those values.

If you really can't pass the data through some ASP or ASP.NET page on the way in--or some equivalent filter--then it might be best to insert it as-is into a temporary table and then run a query that puts it into the real table in the right format.

Example:

INSERT INTO realTable ( datefield )
SELECT CONVERT( DATETIME, LEFT(df,2) + '/' + SUBSTRING(df,3,2) + '/' + RIGHT(df,4), 101 )
FROM tempTable



By the way: Moderator, this belongs in the "Other database" forum.

[AustinV]

worked perfectly, thanks!