1

I have two tables.

  1. NEW [contains data] [all columns are varchar]

  2. NEW2 [empty table] [columns are of different data types]

I want to copy all data from New to New2.

What i did is, 

SELECT T.* 
INTO   #tmp 
FROM   (SELECT * 
        FROM   [dbo].[new]) AS T

then

INSERT INTO New2(col1, col2....)
SELECT *
FROM #TMP

But its not working.

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.
The statement has been terminated.

[what I want is to change the column data types of NEW table, especially the varchar to smalldatetime. So I tried this way. Any other approach is also welcome.]

Any help would be greatly appreciated.

Thank You.

Aditya
  • 2,299
  • 5
  • 32
  • 54
  • 1
    While selecting data from table `#TMP` you need to convert data with corresponding datatype of table `new2`.. in sql-server database you can use `convert` function for same task – pratik garg Dec 06 '13 at 07:40
  • But the table is having lot of columns. My purpose is to only convert column data type which is directly not possible. The query would be too lengthy to apply CONVERT() to each column. So any more ideas. Thanks for fast reply. – Aditya Dec 06 '13 at 07:43
  • 1
    having very large number of column list in table is recommended, that's why we used to have Normalization concept applied on large database to maintain our data in proper way.. But for this problem, according to my knowledge, you need to write that bulky query to insert into another table. – pratik garg Dec 06 '13 at 07:50
  • 1
    As per recommendation you should have same datatype for same kind of columns.. So you can change datatype in either table to make it in sync that would be better approach .. – pratik garg Dec 06 '13 at 07:51
  • Yes Pratik, I agree, but the data that I have loaded in New table is imported from Excel file. So avoid complexity all the columns are set to varchar. The problem is with the date columns only. I tried like CAST([AlarmTime] AS smalldatetime) but it is not working. – Aditya Dec 06 '13 at 07:59
  • Please post a sample value from your date column which is in excel.I mean is that [YYYYDDMMHHMM]? – Deepshikha Dec 06 '13 at 08:13
  • Please describe your columns data types – Saman Gholami Dec 06 '13 at 08:17
  • The date value in excel file is like '2/8/2013 15:00' . I used import wizard to import data to sql server with changing data types [in import wizard edited the generated sql]. but the error was, potential loss of data & aborted. I also tried to change the data type in excel file but then it showed date something like this '45767889.8779'. – Aditya Dec 06 '13 at 08:18
  • @Aditya what is the format of your date in database? Is it `'45767889.8779'` or in `'2/8/2013 15:00'` this format . – Suraj Singh Dec 06 '13 at 08:47

4 Answers4

2

Yes. Done.

What I did is, Imported Excle data in SQL Server table with all columns in a table as varchar data type. The problem was in excel data, the date values, somewhere was NA. So I had to replace all those NA values with null.

To check for those invalid date values in a table, I used following command.

SELECT ISDATE(COL_NAME) AS Result

SELECT ISNULL(COL_NAME) AS Result

For this, sometime you have to also check & set for the date format of SQL Server using following commands,

DBCC useroptions
SET DATEFORMAT mdy

Then all the result values I replaced them with NULL as 

UPDATE TABLE SET COLUMN = NULL WHERE ISDATE(COLUMN) = 0 OR COLUMN = 'NA'

At last I updated required columns manually using simple alter commands as,

ALTER TABLE ALTER COLUMN COL_NAME <<data type>>

I also changed my dateforamat to dmy which prior was mdy.

Thank for Suraj Singh, Deepshikha for their helpful suggestions.

Aditya
  • 2,299
  • 5
  • 32
  • 54
  • HI aditya. can you pls share the codes how you have solved this approach. – coddey May 03 '14 at 08:52
  • @coddey, I have added code/SQL snippests as you asked. I am asking you to refer those – Aditya May 05 '14 at 10:32
  • please check out this link http://stackoverflow.com/questions/23441406/dynamic-casting-of-data-from-one-table-to-another-using-sql-server i need to achieve that. – coddey May 05 '14 at 10:58
1

While inserting cast your column to smalldatetime 

SET DATEFORMAT ymd 
INSERT INTO New2(col1, col2....)
SELECT Col1,Col2 , CAST('2007-05-08 12:35:29'  AS smalldatetime) As Col_Name,...Col3
FROM #TMP
Suraj Singh
  • 4,041
  • 1
  • 21
  • 36
  • Same error not working. I tried with CAST([col_name] AS smalldatetime) – Aditya Dec 06 '13 at 07:57
  • @Aditya what datatype your new table expects ?Also check the your column value if it is valid and can be convertible to `smalldatetime` – Suraj Singh Dec 06 '13 at 07:59
  • hey sorry, the actual error was, [The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.]. When I used sql = 'select cast(col as smalldatetime) from #tmp' – Aditya Dec 06 '13 at 08:01
  • What i did is, sql='INSERT INTO New2([Col]) SELECT CAST(Col as smalldatetime) AS Col FROM #TMP'. But throwing same error. – Aditya Dec 06 '13 at 08:06
  • Even I can not select sql=' select CAST(col as smalldatetime) from #tmp ' Error= Conversion failed when converting character string to smalldatetime data type. – Aditya Dec 06 '13 at 08:09
  • Try this `SET DATEFORMAT ymd SELECT CONVERT(SMALLDATETIME, '03/28/2011 18:03:40')` – Suraj Singh Dec 06 '13 at 08:13
  • @Aditya Try to change youe dateFormat.\ – Suraj Singh Dec 06 '13 at 08:16
  • Hey thanks for your suggestion. helped me alot. plz check my answer what i did. – Aditya Dec 07 '13 at 05:22
1

Try as:

DECLARE @NEW TABLE([date] VARCHAR(20));

INSERT @NEW SELECT '2/8/2013 15:00' ;

select LEFT([date],2) + SUBSTRING([date],3,2) + SUBSTRING([date],5,4) + ' '+ RIGHT([date],5)+':00'
from @NEW 

UPDATE @NEW SET [date] = CONVERT(CHAR(16), CONVERT(SMALLDATETIME, 
                         LEFT([date],2) + SUBSTRING([date],3,2) + SUBSTRING([date],5,4) + ' '+ RIGHT([date],5)+':00', 120));

SELECT [date], CONVERT(SMALLDATETIME, [date]) FROM @NEW;
Deepshikha
  • 9,896
  • 2
  • 21
  • 21
  • Hey thanks Deepshikha. But it is also throwing an error :[ (65535 row(s) affected) (65535 row(s) affected) Msg 295, Level 16, State 3, Line 9 Conversion failed when converting character string to smalldatetime data type. ] – Aditya Dec 06 '13 at 09:40
  • Hey thanks for your suggestion. helped me alot. plz check my answer what i did. – Aditya Dec 07 '13 at 05:22
0

Try This

SET DATEFORMAT ymd 
INSERT INTO destination_table(column_name)
SELECT Column_name As Aliace_name
FROM Source_table
Saksham
  • 9,037
  • 7
  • 45
  • 73
kselva
  • 193
  • 2
  • 3
  • 11