kukiya
asked on
Convert datetime to int
How do I convert a field with the type of datetime to int ?
I need to convert the data it contains too.
Thanks !
I need to convert the data it contains too.
Thanks !
If you use enterprise manager it will do all the graft for you.
ASKER
I need an SQL query to do so...
OK well given that ALTERing columns in tables with the ALTER statement requires that the datatype can be impolicitly cast from old to new the only way to do this is to select all your data into a temporary table
truncate the original table then alter the table and select it bacck in again.
This is infact exactly what enterprise manager does.
So.....
assuming a 1 column table for simplicity,
SELECT * INTO ##TempTable FROM MyTable
TRUNCATE TABLE MyTable
ALTER TABLE MyTable ALTER COLUMN MyColumn INT
INSERT MyTable SELECT CONVERT(INT.MyColumn) FROM MyTable
truncate the original table then alter the table and select it bacck in again.
This is infact exactly what enterprise manager does.
So.....
assuming a 1 column table for simplicity,
SELECT * INTO ##TempTable FROM MyTable
TRUNCATE TABLE MyTable
ALTER TABLE MyTable ALTER COLUMN MyColumn INT
INSERT MyTable SELECT CONVERT(INT.MyColumn) FROM MyTable
ASKER
I run you script and the following error occured :
Line 4: Incorrect syntax near '.'.
This is becuase of INT.MyColumn I guess...
Line 4: Incorrect syntax near '.'.
This is becuase of INT.MyColumn I guess...
sorry CONVERT(INT.MyColumn) should be CONVERT(INT,MyColumn)
ASKER
ok, Thanks.
Now.. another error occures:
"Insert Error: Column name or number of supplied values does not match table definition."
Now.. another error occures:
"Insert Error: Column name or number of supplied values does not match table definition."
INSERT MyTable SELECT CONVERT(INT.MyColumn) FROM MyTable
in this insert statement you will need to add all the original columns but substitute your columun you want to convert with the CONVERT bit.
EG:
for 2 columns:
INSERT MyTable SELECT myfirstcolumn,CONVERT(INT. MyColumn) FROM MyTable
in this insert statement you will need to add all the original columns but substitute your columun you want to convert with the CONVERT bit.
EG:
for 2 columns:
INSERT MyTable SELECT myfirstcolumn,CONVERT(INT.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually, you can do this different ways.
1) Add a new column to contain the converted value, set the new column to the conversion of the old column; drop the old column; rename the new column. For example:
ALTER TABLE yourTable
ADD tempCol INT
UPDATE yourTable
SET tempCol = CAST(dateCol AS INT)
IF @@ERROR <> 0
GOTO errMsg
ALTER TABLE yourTable
DROP COLUMN dateCol
EXEC sp_rename 'yourTable.tempCol', 'dateCol', 'COLUMN'
GOTO done
errMsg:
PRINT 'Error(s) during UPDATE, cannot complete processing'
done:
1) Add a new column to contain the converted value, set the new column to the conversion of the old column; drop the old column; rename the new column. For example:
ALTER TABLE yourTable
ADD tempCol INT
UPDATE yourTable
SET tempCol = CAST(dateCol AS INT)
IF @@ERROR <> 0
GOTO errMsg
ALTER TABLE yourTable
DROP COLUMN dateCol
EXEC sp_rename 'yourTable.tempCol', 'dateCol', 'COLUMN'
GOTO done
errMsg:
PRINT 'Error(s) during UPDATE, cannot complete processing'
done:
2) If the table has a primary key column(s) [or column(s) that uniquely identify a row even if not explicitly a PK], then you can create another holding table with just the PK and the new values, then use that to UPDATE the original table. For example:
BEGIN TRANSACTION
CREATE TABLE dbo.yourTableConversion (id INT, dateColConv INT)
INSERT INTO yourTableConversion
SELECT id, CAST(dateCol AS INT) AS dateColConv
FROM yourTable
IF @@ERROR <> 0
GOTO errMsg
ALTER TABLE yourTable
DROP COLUMN dateCol
ALTER TABLE yourTable
ADD dateCol INT
UPDATE yourTable
SET dateCol = dateColConv
FROM yourTable
INNER JOIN yourTableConversion ytc ON yourtable.id = ytc.id
GOTO done
errMsg:
PRINT 'Error(s) during INSERT, cannot complete processing'
done:
BEGIN TRANSACTION
CREATE TABLE dbo.yourTableConversion (id INT, dateColConv INT)
INSERT INTO yourTableConversion
SELECT id, CAST(dateCol AS INT) AS dateColConv
FROM yourTable
IF @@ERROR <> 0
GOTO errMsg
ALTER TABLE yourTable
DROP COLUMN dateCol
ALTER TABLE yourTable
ADD dateCol INT
UPDATE yourTable
SET dateCol = dateColConv
FROM yourTable
INNER JOIN yourTableConversion ytc ON yourtable.id = ytc.id
GOTO done
errMsg:
PRINT 'Error(s) during INSERT, cannot complete processing'
done:
ASKER
ShogunWade,
I added the name of the fileld but still got an error:
Disallowed implicit conversion from data type datetime to data type int, table 'MyDb.dbo.MyTable', column 'MyColumn '. Use the CONVERT function to run this query.
I added the name of the fileld but still got an error:
Disallowed implicit conversion from data type datetime to data type int, table 'MyDb.dbo.MyTable', column 'MyColumn '. Use the CONVERT function to run this query.
ASKER
ScottPletcher,
I tried your fist code. It does work great,
but the value in the new int filed is: 37958, which is 1/1/1970.
The coversion does not give the correct value of the orginal field...
Thanks !!!
I tried your fist code. It does work great,
but the value in the new int filed is: 37958, which is 1/1/1970.
The coversion does not give the correct value of the orginal field...
Thanks !!!
If you want the number of days from a given point then you need to use DATEDIFF(day,@FixedDate,My Field)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I translate 37958 to CTime in my MFC code
CTime time(37958);
time.Format("'%m/%d/%Y %H:%M:%S'");
time will be: 1/1/1970 12:32:38
CTime time(37958);
time.Format("'%m/%d/%Y %H:%M:%S'");
time will be: 1/1/1970 12:32:38
The reason for this is that different languages use a different offset.
I am confused as to why you would want to convert the date to an integer only to convert it back again in the client app, isnt this wasteful?
I am confused as to why you would want to convert the date to an integer only to convert it back again in the client app, isnt this wasteful?
ASKER
its just that I need to adapt a certain DB to an application.
The DB field should be int, and the application should present it as a date.
The DB field should be int, and the application should present it as a date.
in that case you will need to decide your own fixed offest for the integer and calculate dates using DATEADD in sql or the equivelent in any other language used.