Updating column in a table with it's own column with Int to Datetime conversion

Question

Updating column in a table with it's own column with Int to Datetime conversion

sunshine-587009

SSCrazy Eights

Points: 8981
More actions
March 15, 2011 at 1:18 pm

#236405

HI everyone,
I don't normally code, but our developers are quite bombarded. I'm trying to update a column from a table that looks like this:
ColumnName Datatype Sample Data
ID1 int 1
ID2 int 1
ID3 int 1
as_of_date int 20110315
as_of_date2 datetime I would like to update this to read 2011-03-15 00:00:00.000
I'm using this code:
UPDATE TableName
SET TableName.as_of_date2
= (SELECT CONVERT(datetime, as_of_date)
FROM TableName AS F1
WHERE TableName.local_item_key =F1.local_item_key
AND TableName.company_id=F1.company_id);
And receiving this error:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
Any help appreciated!
¤ §unshine ¤

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the logic for the new date column? How does it relate to the other columns?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Try this:

UPDATE #TableName

SET AS_OF_DATE2 = CAST ( STUFF( STUFF (AS_OF_DATE,5,0,'-') , 8 ,0,'-') AS DATETIME)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

GilaMonster (3/15/2011)
What's the logic for the new date column? How does it relate to the other columns?

Gail, i guess the OP is trying to convert the as_of_date in the table to a datetime value and update the as_of_date2 column.

Sunshine,

I have questions for you

1. Why in first place you are storing date as an INT column? Dates SHOULD be stored as DATETIME or if you wnat granularity, you can have it as DATE and TIME (as you have posted in SQL 2008 forum , i am assuming u have SQL 2008 instance)

2. Why do you want to create a new column , cant you just pass converted value directly to the UI/ calling code instead of having a separate for that?

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

In spite of the very legitimate questions about your architecture I believe the following should get you what you need.

update TableName set asofdate2 = Cast(cast(asofdate as varchar) as datetime)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 5

LIke I stated earlier, I'm not a developer, I'm a DBA and passing things through and changing code from 3 different sources, is not my forte.Unfortunately, I did not design this 400 million row table and trying to change the int to the datetime. The simplest form I find is to create a new column and move the data over without disrupting processes already running on it and no additional space. I'd then be renaming and dropping the old column. Next is partitioning it... 🙂

Thank you soooo much for your help!

¤ §unshine ¤