• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

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(StartTime) as varchar(2))))) + cast(month(StartTime) as varchar(2))
2 Solutions
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, a case can be made that a physical table should be used for 77 million rows.

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.
Scott PletcherSenior DBACommented:
Period is varchar, right?  Then that UPDATE almost certainly requires every page to be split: yikes, yes, that will take a lot of time.

Just add a computed column for Period that generates the YYYY-DD format you need, but don't actually store it.  (Alternatively, you could pre-pad the Period column with '???????'; then, when you UPDATEd it, the page wouldn't have to split, and it would be much faster, although still not as fast as using a computed column.)

ALTER TABLE #sometable
ADD Period AS CONVERT(varchar(7), StartTime, 120)
IF using SQL Server 2012 upward

    format(@StartTime ,'yyyy-MM')

    convert(varchar(7), StartTime ,120)    -- NB same as Scott above

Does this field get indexed?

if yes: why not use an integer instead?

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now