Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
smorty44
Yak Posting Veteran
93 Posts |
 Posted - 2007-12-14 : 15:44:43
|
| I have a table with an int field that I'm trying to insert into a datetime field, however, there are 0's in the int field. How do I write a case statement to change the 0's to '01/01/1900' and then store the datetime field as 'mm/dd/yyyy'? The data is currently coming in as yyyymmdd as int.Sample Data:197205181972052319720523197206011972060319720609 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-14 : 15:47:28
|
| Can't you just convert it directly? I don't see how the zeroes are causing a problem when your current format is yyyymmdd.INSERT INTO YourTable(YourDateTimeColumn)SELECT CONVERT(datetime, YourIntColumn)FROM YourOtherTableTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
smorty44
Yak Posting Veteran
93 Posts |
Posted - 2007-12-14 : 15:54:01
|
| No, when I run that I get this error.Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.However, I got this to work:Insert into table select case when pat_dob = 0 then '01/01/1900' else (Convert(char(10), cast(cast(pat_dob as char(10)) as datetime), 101)) end from othertable |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
convert int to datetime