What's an efficient way to convert month and year string to a date?
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?
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.
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
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
精彩评论