Solved

# Extracting period from Date Column

Posted on 2014-09-24
280 Views
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))
0
Question by:dthansen

LVL 65

Expert Comment

ID: 40342584
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.
0

LVL 69

Accepted Solution

Scott Pletcher earned 300 total points
ID: 40342936
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)
0

LVL 48

Assisted Solution

PortletPaul earned 200 total points
ID: 40343725
IF using SQL Server 2012 upward

format(@StartTime ,'yyyy-MM')

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

Does this field get indexed?

if yes: why not use an integer instead?
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.