dthansen
asked on
Extracting period from Date Column
The following query runs for about 15 minutes on a 77,000,000 row temporary table. This is too long. I expect there is a more efficient to do this without the replicate() and cast() functions. Any help is appreciated.
update #sometable set Period = cast(year(StartTime) as char(4)) + '-' + replicate('0', 2 - len(rtrim(cast(month(Start Time) as varchar(2))))) + cast(month(StartTime) as varchar(2))
update #sometable set Period = cast(year(StartTime) as char(4)) + '-' + replicate('0', 2 - len(rtrim(cast(month(Start
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Before we jump to conclusions, give us a data mockup of what StartDate looks like in the table (datetime?), data type of period, and what period needs to look like.
<wild guess>
UPDATE #sometable
SET Period = CAST(year(StartTime) as char(4)) + '-' + RIGHT('0' + CAST(month(StartTime) as varchar(2)),2)
Also, I have an article out there on How to Build your own SQL calendar table to perform complex date expressions. If you had a calendar table with this YYYY-MM value as a column, to do an UPDATE on 77 million rows all you'd need to do is JOIN on the date, and not to perform the expression 77 million times.