What's an efficient way to convert month and year string to a date?

Question

I have column with varchar values like "022008" that I need to convert into a datetime like "02/01/2008". This will run on millions of records for a conversion process. What is an efficient select statement for doing this?

Answer 1

Have a look here:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

The STR_TO_DATE function does just what it sounds like it would do: it takes a string, and turns it into a standard date object. To get it into the MM/DD/YYY format, you use DATE_FORMAT. This is the simplest way to do this, and is probably the most efficient.

For your example, you can convert it like so:

SELECT DATE_FORMAT(STR_TO_DATE('022008', '%m%Y'), '%m/%d/%Y');
-> 02/00/2008

You'll have to do some extra nonsense to get the day to read "01", so perhaps you could clean this up once you get the results back; otherwise, the query becomes more complicated.

Hope this helps.
-tjw

Answer 2

Not hugely efficient, but my first stab at it:

declare @s as char(6)
set @s = '031977'

select dateadd(month, cast(substring(@s, 1, 2) as int) - 1, dateadd(year, substring(@s, 3, 4) - 1900, 0))

Output:

1977-03-01 00:00:00.000

Answer 3

How about something like:

SET DATEFORMAT MDY
GO
Select StringValue, Cast(Stuff(StringValue, 3, 0, '-01-') As datetime)
From Table...

On a million rows on my local machine, that returned in 10 seconds.